The SMALL function takes two arguments, array and k. Array is an array or range of numeric values. The argument k represents position or rank. For example, to return the smallest value in array, provide 1 for k. To return the fifth smallest value in array, provide 5 for k. To get nth largest values, see the LARGE function.
Examples
In the formula below, the SMALL function returns the third smallest value in a list of five numbers provided in an array constant: Note values do not need to be sorted. To retrieve the 1st, 2nd, and 3rd smallest values in a range: In the example shown, the formulas in G5, G6, and G7 are, respectively: See below for more advanced formulas based on the SMALL function.
Notes
SMALL ignores empty cells, text values, and TRUE and FALSE values. If array contains no numeric values, SMALL returns a #NUM! error. To determine the rank of a number in a data set, use the RANK function.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
