Excel : Lookup Top N values ignoring duplicates


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($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))    <=>     =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)

=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 it)

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

7/100000
8/100000
9/100000
.
.
.
16/100000

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.

0.97-(7/100000)
0.69-(8/100000)
.
.
0.6-(16/100000)

It gives us unique values.

Spread the Word!
Share
Related Posts
About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he has worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and Human Resource.

4 Responses to "Excel : Lookup Top N values ignoring duplicates"
  1. Thanks for sharing this excellent formula.

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

    ReplyDelete
  3. Hi Thanks for the file. i dont want to ignore duplicate, lets say if we have following data: 10, 10, 10, 9, 9,8 then it should generate 10,9,8

    ReplyDelete
  4. What's the use of ROWS($1:1)

    ReplyDelete

Next → ← Prev

Looks like you are using an ad blocker!

To continue reading you need to turnoff adblocker and refresh the page. We rely on advertising to help fund our site. Please whitelist us if you enjoy our content.