Article updated on February 15, 2018

The SMALL function returns the k-th smallest value from a group of numbers.

Example, formula in cell E3 returns 5 because it is the third smallest number in cell range B3:B7. 2, 3, 5, 5 and 6.

Excel Function Syntax

SMALL(array, k)

Arguments

array Required. A group of numbers you want to extract the k-th smallest number from.
k Required. k-th value, 1 returns the smallest number, 2 returns the second smallest number etc.

 

Example 1 - Cell range contains numbers, text and blanks

SMALL1

=SMALL(B3:B10, 3)

becomes

=SMALL({3; 6; "A"; 5; "X"; 0; 4; 4},3)

Text strings and blanks are overlooked. The array becomes

=SMALL({3; 6; ; 5; ; ; 4; 4},3)

and returns 4. 4 is the third smallest numerical value in the array.

Example 2 - Use a condition (Array formula)

SMALL3

The following array formula looks for the second smallest value in column C if corresponding value in adjacent column D is 2012-Dec:

=SMALL(IF(B4:B14=F3, C4:C14, ""), F4)

becomes

=SMALL(IF({"2013-Jan"; "2012-Dec"; "2013-Jan"; "2012-Dec"; "2012-Nov"; "2013-Jan"; "2013-Jan"; "2012-Dec"; "2013-Jan"; "2012-Nov"; "2012-Dec"}="2012-Dec", C4:C14, ""), F4)

becomes

=SMALL(IF({FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}, C4:C14, ""), F4)

becomes

=SMALL({"";60.69;"";8.08;"";"";"";24.44;"";"";2.25}, 2)

and returns 8.08 in cell F7.

Download excel *.xlsx file

SMALL and LARGE function.xlsx