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

 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

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

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

Let's Get Connected: Email | LinkedIn