Summary

SMALL returns the k-th smallest value in a dataset. It does more than MIN because it lets you ask for the 2nd, 3rd, or 5th smallest value instead of only the single lowest one.

That makes it useful for comparison lists, budget options, fastest times, and low-end thresholds. If you want to look beyond the absolute minimum, SMALL gives you a ranked view from the bottom of the dataset.

Syntax

=SMALL(array, k)

array is the set of numbers to rank, and k is the position from the smallest. So 1 means smallest, 2 means second smallest, and so on.

Arguments

• array - [required] The range or array of numeric data to rank.
• k - [required] The position to return from the bottom of the list.

SMALL vs Other Functions

Using the SMALL Function

SMALL is handy when the minimum is too extreme to be the only reference point. For example, the absolute cheapest supplier quote may be unusual, but the second or third cheapest price might be a more realistic benchmark. The same idea applies to times, costs, scores, and many other datasets.

Microsoft notes that SMALL returns #NUM! if the array is empty. The same error appears if k is less than or equal to zero, or if it is greater than the number of data points. If n is the number of values, then SMALL(array,1) gives the smallest value and SMALL(array,n) gives the largest value.

Duplicate values stay in the ranking. If the two lowest values are both 10, then the first and second ranked results can both be 10. That is expected because SMALL returns the value at the requested position, not only unique values.

Example 1 - Find the 2nd smallest value

This is useful when you want the next cheapest or next fastest result instead of the single absolute minimum.

=SMALL(B1:B10,2) // Returns the second smallest value in the range.

Example 2 - Use SMALL as a min function

When k is 1, SMALL returns the same result as MIN.

=SMALL(B1:B10,1) // Returns the smallest value in the range.

Example 3 - Return the 3rd smallest result

This is useful for ranking low-end values without having to sort the original data.

=SMALL(B1:B10,3) // Returns the third smallest value in the range.

Example 4 - Use a deeper low rank as a benchmark

A value like the 5th smallest can be more useful than the absolute minimum when you want a realistic lower-end target.

=SMALL(B1:B10,5) // Returns the fifth smallest value in the range.

Conclusion Recap

• Main job: SMALL returns the k-th smallest value in a dataset.
• k controls the rank: 1 means smallest, 2 means second smallest, and so on.
• Useful for ranked low-end analysis: It helps when you want more than just the minimum.
• Duplicates count: Repeated low values can appear in multiple ranking positions.
• Error case: Excel returns #NUM! if the array is empty or k is outside the valid range.
• Relationship to MIN: SMALL(array,1) gives the same result as MIN.