SMALL2SMALL(array, k)

Returns the k-th smallest value in a data set.

LARGE(arrayk)

Returns the k-th largest value in a data set.

Arguments

array - Is a reference to a cell range or is an array of constants.

k - The position in the array of the value to return.

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