## Summary

The Excel SMALL function returns a numeric value based on its position in a list when sorted by value in ascending order. In other words, SMALL can return the "nth smallest" value (1st smallest value, 2nd smallest value, 3rd smallest value, etc.) from a set of numeric data.

## Purpose

Get nth smallest value

## Return value

The nth smallest value in the array.

## Syntax

``=SMALL(array,k)``
• array - An array or range of numeric values.
• k - Position as an integer, where 1 corresponds to the smallest value.

## How to use

The SMALL function returns a numeric value based on its position in a list when sorted by value in ascending order. In other words, SMALL can return the "nth smallest" value (1st smallest value, 2nd smallest value, 3rd smallest value, etc.) from a set of numeric data.

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:

``````=SMALL({29,14,33,19,17},3) // returns 19
``````

Note values do not need to be sorted. To retrieve the 1st, 2nd, and 3rd smallest values in a range:

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

In the example shown, the formulas in G5, G6, and G7 are, respectively:

``````=SMALL(D5:D16,1) // returns 66
=SMALL(D5:D16,2) // returns 69
=SMALL(D5:D16,3) // returns 71
``````

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. Author ### 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.