Find the 2nd Smallest Number in a List

When summarising data in Excel it is common for you to need to find the smallest numbers in a list. You have probably used the MIN function before to find the smallest number. But how do you find the second, third or fourth smallest?

The SMALL function can be used to find the smallest numbers in a list. Let’s use the SMALL function to find the 2^nd and 3^rd smallest numbers in the list below.

For the SMALL function to work, you need to supply the cell range and the rank of the number you want returned.

To return the 2^nd smallest number in cell E2, enter:

=SMALL(B2:B8,2)

The B2:B8 represents the list you want to search within.

The 2 represents the rank of the number returned i.e. second smallest.

To find the third largest number in the list you would then enter the function below in cell E3.

=LARGE(B2:B8,3)

The number 2 was changed to a 3 to return the third smallest number.

Functions like VLOOKUP, INDEX and MATCH can then be used to return related information about the 2^nd or 3rd smallest values such as the companies name.

Find more Microsoft Excel functions.

Use the MATCH function with VLOOKUP

Use INDEX and MATCH in Excel