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)

## 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:**

**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.- 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. - 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.
- The subtraction operation generates unique values in the range, allowing you to perform operations or comparisons based on those unique values.

Thanks for sharing this excellent formula.

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

ReplyDeleteHi 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

ReplyDeleteWhat's the use of ROWS($1:1)

ReplyDelete