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.

**Read More:** How to Create Top 10 List with Duplicates in Excel

**Download the Practice Workbook**

## Related Articles

- How to Check If a Value is in List in Excel
- Lookup Value in Column and Return Value of Another Column in Excel
- Find Text in Excel Range and Return Cell Reference
- How to Search Text in Multiple Excel Files
- [Solved!]
**CTRL+F**Not Working in Excel

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

Great article. Congratulations

Many thanks

Hi Luis,

Thank you for your response. Hope this comes in handy for more people like you.

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!

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

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!

Hey

Philip,Thanks for your response. You can use the

SORTandFILTERfunctions along with theLARGEfunction 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