Exceljet

Quick, clean, and to the point

Excel SMALL Function

Excel SMALL function
Summary 

The Excel SMALL function returns numeric values based on their position in a list ranked by value. In other words, it can retrive "nth smallest" values - 1st smallest value, 2nd smallest value, 3rd smallest value, etc.

Purpose 
Get the nth smallest value
Return value 
The nth smallest value in the array.
Syntax 
=SMALL (array, n)
Arguments 
  • array - A range of cells from which to extract smallest values.
  • n - An integer that specifies the position from the smallest value, i.e. the nth position.
Version 
Usage notes 

The SMALL function returns numeric values based on their position in a list when sorted by value. In other words, SMALL can retrieve "nth smallest" values - 1st smallest value, 2nd smallest value, 3rd smallest value, etc.

The SMALL function is useful when you want to retrieve the nth lowest value from a set of data — for example, the first, second, or third fastest times in a race, as seen in the screenshot above.

To get nth largest values, see the LARGE function.

Example

To retrieve the 1st, 2nd, and 3rd smallest values in a range:

=LARGE(SMALL,1) // 1st smallest value
=LARGE(range,2) // 2nd smallest value
=LARGE(range,3) // 3rd smallest value

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.