Excel SMALL Function
SMALL returns the nth smallest numeric value from a range or array.
Use it when you need the 2nd, 3rd, or 10th lowest value from a list without sorting the data, such as costs, fastest times, or earliest dates.
SMALL syntax & arguments
Syntax
=SMALL(array, k)
-
1
array
RequiredThe numeric values, range, or array to rank from smallest to largest.
-
2
k
RequiredThe position to return from the sorted values. Use
1for the smallest value,2for the second smallest, and so on.
Example
=SMALL(E2:E25, 3)
Return the third smallest numeric value from E2:E25.
SMALL caveats
-
k is a position, not a percentage
kof1returns the same value as MIN;kof2returns the second smallest value. -
Ties occupy separate positions
If the two smallest values are both 5,
SMALL(range, 1)andSMALL(range, 2)can both return 5. -
k must be within the numeric list
A
kvalue less than 1 or greater than the number of numeric values returns a#NUM!error. -
Text and blanks are ignored
Non-numeric cells do not count toward the ranked positions.
-
Errors pass through
Error values in the array pass through, so SMALL returns the error instead of a ranked value.
Need the high end instead? Use LARGE for nth largest values.
Intro SMALL practice problem
Solve the intro problem directly here, or open it on its own page.
Advanced SMALL practice problems
Use SMALL alongside other Excel functions in realistic, less-prescriptive challenges.
