How to Get Top 10 Values Based on Criteria in Excel

We’ll use a dataset with student Names and marks in Physics, Chemistry and History. Note that this article uses Excel 365. Some functionalities may be missing in older Excel versions.

Method 1 – Find Top 10 Values Based on a Single Criterion in Excel

Case 1.1 – Insert a Combination of LARGE, IF, and ROW Functions

Let’s calculate the top 10 marks in physics for students who have more than 60 in chemistry.

Select Cell G6 and insert the following formula there.

=LARGE(IF(D6:D20>60,C6:C20,""),ROW(A1:A10))

Press Enter.
We will see the top 10 marks of physics in the range of cells G6:G15.

ROW(A1:A10) creates an array of numbers 1 to 10.
IF(D6:D20>60, C6:C20,””) checks the condition D6:D20>60, if the condition is met then it gives the output as a cell in the range of C6:C20, if criteria are not met it gives an empty value.

Case 1.2 – Apply XLOOKUP, LARGE, and FILTER Functions

Let’s extract the names of the students with the highest scores in Physics.

Select Cell G6 and insert the following formula there.

=XLOOKUP(LARGE(FILTER(C6:C20,D6:D20>60),ROW(A1:A10)),C6:C20,B6:B20)

Hit Enter.
We will see the Name of the students who got top 10 numbers in Physics.

LARGE(FILTER(C6:C20,D6:D20>60),ROW(A1:A10)) gives the lookup value for the XLOOKUP function. The Filter function inside works same as the IF function.
C6:C20 is the lookup_array and B6:B20 is the return_array.

Case 1.3 – Use INDEX-MATCH Combination

Here’s an alternative that doesn’t use XLOOKUP.

Select Cell G6 and write the following formula there.

=INDEX(B6:B20,MATCH(LARGE(FILTER(C6:C20,D6:D20>60),ROW(A1:A10)),C6:C20,0),1)

Press Enter.
We will see the Names of students who got Top 10 Marks in Physics.

ROW(A1:A10)) gives an array of 1 to 10.
FILTER(C6:C20,D6:D20>60) checks the condition D6:D20>60 and gives output from C6:C20.
The LARGE function takes the above parts as arguments and gives the output to 10 numbers.

Method 2 – Get Top 10 Values Based on Multiple Criteria in Excel

Let’s get the top 10 marks in physics that satisfy two conditions: the student has more than 50 in history and more than 60 in chemistry.

Select Cell G6 and use the following formula there:

=LARGE(IF((--D6:D20>60)*(--E6:E20>50),C6:C20,""),ROW(A1:A10))

Hit Enter.

ROW(A1:A10) creates an array of 1 to 10.
IF((–D6:D20>60)*(–E6:E20>50),C6:C20,””) checks for the conditions and gives the output from the cell range C6:C20.

More Examples of Finding Top 10 Values in Excel

Example 1 – Use of the LARGE and ROW Functions to Get Top 10 Numbers

Use the following formula:

=LARGE($C$6:$C$20,ROW(A1:A10))

Note: The formula contains the LARGE and the ROW functions which work as described in the methods before.

Example 2 – Apply the LARGE and COLUMN Functions for Finding the Top 2 Values in a Row

Insert the following formula in the first result cell and hit Enter.

=LARGE($C6:$E6,COLUMN(A1:B1))

Note: The formula is similar to the previous example, except that it uses the COLUMN function (works along the row) instead of the ROW function.

Example 3 – Identify Top 10 Numbers Using the SORT and FILTER Functions

Use the following formula for cell G6.

=SORT(FILTER(C6:C20,C6:C20>=LARGE(C6:C20,10)),1,-1)

FILTER(C6:C20,C6:C20>=LARGE(C6:C20,10)) finds the top numbers.
The SORT function rearranges the numbers in descending order.

Example 4 – Utilize INDEX and MATCH Functions to Find Names That Correspond to Top 10 Values

Select Cell G6 and enter the following formula:

=INDEX(B6:B20,MATCH(LARGE(C6:C20,ROW(A1:A10)),C6:C20,0),1)

B6:B20 is the reference number for the INDEX function.
MATCH(LARGE(C6:C20,ROW(A1:A10)),C6:C20,0) gives the row number and 1 is the column number of the INDEX function.

Example 5 – Use XLOOKUP and LARGE Functions to Get the Top 10 Numbers

Select Cell G6 and insert the following, then press Enter.

=XLOOKUP(LARGE(C6:C20,ROW(A1:A10)),C6:C20,B6:B20)

How to Identify Top 10 Values with Duplicates in Excel

Select Cell G6 and insert the following formula there.

=LARGE(UNIQUE(E6:E20),ROW(A1:A10))

Hit Enter.

UNIQUE(E6:E20) finds the unique values that act as the argument for other functions in the formula.

Download the Practice Workbook

Top 10 Values Based on Criteria.xlsx

<< Go Back to Find Value in Range | Excel Range | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

6 Comments

Luis Mar 14, 2022 at 9:13 PM

Great article. Congratulations
Many thanks
- Reply
  Rifat Hassan Mar 15, 2022 at 1:32 AM
  
  Hi Luis,
  Thank you for your response. Hope this comes in handy for more people like you.
Nico Mar 21, 2022 at 8:46 PM

Great tool but let’s say that you have multiple students with the same mark in Physics. How would you return all their names? Xlookup only returns the first match for that score.

Thanks!
- Reply
  Rifat Hassan Aug 22, 2022 at 3:01 PM
  
  Hi Nico,
  Thanks for your response. You can use the FILTER function of Excel for your problem. Check this article for details https://www.exceldemy.com/excel-filter-multiple-criteria/.
Philip Apr 20, 2023 at 5:57 AM

HELP!!!
Is there a way to look up the top 10 marks for all classes (cols C-E – so starting off with a top 10 table [Large(C6:E20,1), Large(C6:E20,2) and so on].

Then look up the class title and student name for each score… the issue I am finding is with the equal scores (for example, 77 comes up in several classes) – I cannot get it to show the student name & class for 1st instance of 77, or 2nd and so on.

Thanks for any help solving this!
- Reply
  Shamima Sultana Apr 27, 2023 at 8:59 AM
  
  Hey Philip,
  
  Thanks for your response. You can use the SORT and FILTER functions along with the LARGE function to solve your problem.
  Here’s the practice sheet we used. You can check it out for a better understanding.
  SORT-FILTER.xlsx
  You can also check out this article for more detailed explanations.
  https://www.exceldemy.com/excel-top-10-list-with-duplicates/
  
  Regards
  Hassan Shuvo| ExcelDemy Team