# Excel : Lookup Top N values

In this article we will cover how to lookup the top N values in a dataset. If there are multiple entries with the same value, only one instance of that value will be considered in determining the top N values.

Let's take a scenario where you are tasked with identifying the top 5 scorers and their respective scores in class A. It is possible for two or more students to have the same score.

See the table below showing the values which are in cells D6:E16. (Download this data file)
 Names Scores James 97% Dave 69% Sam 97% Rohan 79% Aamir 45% Ranbir 55% Hrithik 74% Shahrukh 74% Bean 37% John 60%
To Find the Top 5 Values :
=LARGE(\$E\$7:\$E\$16,ROW(A1))
LARGE returns the kth largest value in a data set whereas the ROW returns the row number.

=LARGE(\$E\$7:\$E\$16,ROW(A1)) can be read as =LARGE(RANGE,1)

Nested LARGE with ROW function to automatically increments the rank position.When you paste the formula down it automatically finds 1st,2nd,3rd,4th,5th largest values.

## How to find the Top Scorers in Excel

There are two ways to compute the top 5 scores in MS Excel :
1. Non-array formula
=INDEX(\$D\$7:\$D\$16,MATCH(LARGE(INDEX(\$E\$7:\$E\$16+(ROWS(\$E\$7:\$E\$16)-ROW(\$E\$7:\$E\$16))/10^5,0),ROWS(\$1:1)),INDEX(\$E\$7:\$E\$16+(ROWS(\$E\$7:\$E\$16)-ROW(\$E\$7:\$E\$16))/10^5,0),0))
2. Array formula
Press Ctrl Shift Enter to confirm the formula below (instead of just ENTER).
=INDEX(\$D\$7:\$D\$16,MATCH(LARGE(\$E\$7:\$E\$16-ROW(\$E\$7:\$E\$16)/10^5,ROWS(\$1:1)),\$E\$7:\$E\$16-ROW(\$E\$7:\$E\$16)/10^5,0))
Explanation:
1. INDEX-MATCH is a combination of functions in Excel that allows you to find a value in one column of a table and retrieve a corresponding value from another column in the same row.
2. The MATCH function is used to find the position of the first occurrence of a value in a range. To ensure uniqueness in the range, we divide the row numbers by 10^5. For example, ROW(\$E\$7:\$E\$16)/10^5 evaluates to 7/100000, 8/100000, 9/100000, and so on.
3. To create unique values in the range \$E\$7:\$E\$16, we subtract the fraction values obtained in the previous step from each value in the range. For example, \$E\$7:\$E\$16-ROW(\$E\$7:\$E\$16)/10^5 results in 0.97-(7/100000), 0.69-(8/100000), 0.6-(16/100000), and so on.
4. The subtraction operation generates unique values in the range, allowing you to perform operations or comparisons based on those unique values.
Related Posts
Share