Excel : Lookup Top N values ignoring duplicates

Best Online Course : SAS Programming with 50+ Case Studies

- Explain Programming Concepts in Simple English
- Live Projects & Case Studies
- Job Placement Assistance
- Get 10% off till Oct 26, 2017
- Batch starts from October 28, 2017


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.


Excel Tutorials : 100 Excel Tutorials

About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has close to 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like retail and commercial banking, Telecom, HR and Automotive.


While I love having friends who agree, I only learn from those who don't.

Let's Get Connected: Email | LinkedIn

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.

    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

Next → ← Prev