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.
More by this Author
Form controls can be added to an Excel spreadsheet to create interactivity with the user. This article looks at using option button controls to allow a user to choose the data they want to see on a chart. The first...
If you are using pictures in your Excel spreadsheet you may be having some difficulty with sorting the pictures along with the other data in the list. The good news is that you can sort pictures in an Excel list just...
Impaction is when a bearded dragon’s digestive tract has become blocked. It is a very real hazard that is brought about when the beardie has either swallowed something that is too large, or something that it...
No comments yet.