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

Read More: How to Find Top 5 Values and Names in Excel

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

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

1. Great article. Congratulations
Many thanks

2. 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!

3. 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!

Advanced Excel Exercises with Solutions PDF