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 2nd and 3rd 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 2nd smallest number in cell E2, enter:
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.
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 2nd or 3rd smallest values such as the companies name.
Find more Microsoft Excel functions.