Excel: How to Find Top 5 Values and Names


Suppose you have the following dataset in Excel that shows the exam scores for various students and you’d like to find the top 5 scores along with the names of the students with the top 5 scores:

The following example shows how to do so.

Example: Find Top 5 Values and Names in Excel

You can use the following formula to find the largest value in the range B2:B13:

=LARGE($B$2:$B$13,ROWS($C$2:$C2))

We will type this formula into cell D2:

We can see that 99 is the largest value in the Score column.

Next, we can click and drag this formula down to four more cells in column D to find the next four largest values:

Column D now shows the top five scores from the original dataset.

Next, we can type the following formula into cell E2 to extract the name that corresponds to the top score:

=INDEX($A$2:$A$13,MATCH(LARGE($B$2:$B$13,ROWS($C$2:$C2)),$B$2:$B$13,0))

We can then click and drag this formula down to the remaining cells in column E:

We have now extracted the top 5 scores along with the names that correspond to these scores from the original dataset.

Note that we chose an arbitrary range for the ROWS function in both formulas.

You can choose a different cell range to use for ROWS as long as the starting cell is an absolute reference and the ending cell is a relative reference.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

Excel: How to Count If Cells Contain Text
Excel: How to Count Unique Values by Group
Excel: How to Count Unique Values Based on Multiple Criteria

3 Replies to “Excel: How to Find Top 5 Values and Names”

  1. What happens if both Issac and Henry score 91. How do you modify the expression to get both names rather than Henry twice?

  2. Hi, how do I get the name to reflect correctly when the are same score for different names?

    I am faced with a challenge whereby I have same scores for multiple names.

Leave a Reply

Your email address will not be published. Required fields are marked *