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.