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.

Finding the 2nd and 3rd smallest numbers
Finding the 2nd and 3rd smallest numbers

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:

=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.

Use the SMALL function to find he second smallest number
Use the SMALL function to find he second smallest number

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 2nd or 3rd smallest values such as the companies name.

Find more Microsoft Excel functions.

More by this Author

  • Sort Pictures in an Excel List
    0

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

  • Create an Interactive Excel Chart using Option Buttons
    6

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

  • Brock Lesnar Nutrition and Workouts
    2

    Brock Lesnar is a phenomenon of a man and an athlete. Brock Lesnar weighs a lean 265 lbs and yet possesses extreme speed, agility and athleticism. How a man carrying so much muscle mass can maintain such high levels of...


No comments yet.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working