How to Find the Second Smallest Value

In Excel, you can use command from the ribbon quickly find the smallest number, or you can use the SMALL function to find the K-th smallest value from a dataset based on its relative standing.

Example 1: What is the 5th smallest income?

= SMALL(C2:C9,5) The 5th smallest income is $73,069.

Example 2: What is the smallest income?

= SMALL(C2:C9,1) The smallest income is $50,712.

Example 3: What is the 20th smallest income?

= SMALL(C2:C9,20) The result returns to #NUM! error.

There are 8 persons in the sample, so the 20th smallest income does not exist, the SMALL function will return #NUM! error. Similarly, when the array is empty, the SMALL function also returns the #NUM! error.


= SMALL(Array, K)


– The array is required; the data array or range which you need to determine the K-th smallest value;

– K is required; the position from the smallest in the data range.


– K should be in the range from 1 (smallest) to n (largest), where n is the number of data points. Otherwise, if k ≤ 1 or if k is greater than the number of data points, the SMALL function returns the # NUM? error.

– If the array is empty, the SMALL function returns the #NUM! error.

Download: SMALL Function

Leave a Reply