Excel : Lookup Top N values ignoring duplicates

Excel Course : The Ultimate Excel Programmer Course

Imagine you are asked to figure out the top 5 scorers with their scores from class A. Two or more students can have the same score.

Following the values in 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 returns the kth largest value in a data set whereas the ROW returns the row number.
=LARGE($E$7:$E$16,ROW(A1))    <=>     =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.

To Find the Top 5 Scorers :

There are two ways to compute this :

1. Non-array formula ( Press Enter to confirm it)


2. Array formula ( Press Ctrl Shift Enter to confirm it)



1. INDEX- MATCH looks for a value in the right column of a table and returns a value in the same row from a column you specify. 

2. MATCH always the first occurence so we first need to create unique values in range

ROW($E$7:$E$16)/10^5 - This part is used to create unique values in range E7:E16.
It evaluates as follows :


3. $E$7:$E$16-ROW($E$7:$E$16)/10^5

And substract the above fraction values from each values of range E7:E16.


It gives us unique values.

Coursera Data Science

Excel Tutorials : 100 Excel Tutorials

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*

Related Posts:

2 Responses to "Excel : Lookup Top N values ignoring duplicates"

  1. Thanks for sharing this excellent formula.

  2. Nice article. In my experience, ROWS($1:1) won't work in an array formula. It will generate all 1's, unlike with the non-array version. I usually use ROW(INDIRECT("1:5")) to generate that sort of list within an array formula.


Next → ← Prev