In many cases, we may want to find the top values which satisfy one or more conditions from an Excel dataset. There are plenty of Excel functions to do the task. In this article, I will show the way to get the top 10 values based on criteria in Excel with 2 ideal examples. In addition to that, I will also show the procedure to find the top values without any certain criteria.

**Table of Contents**Expand

## How to Get Top 10 Values Based on Criteria in Excel: 2 Ideal Examples

In this section, I will show the ways to get the top **10 **values based on criteria with **2 **ideal examples. One will deal with single criteria, the other one will be on multiple criteria. For demonstration, I have used a dataset having some student’s **Name**, marks of **Physics**, **Chemistry **and **History**.

### 1. Find Top 10 Values Based on Single Criterion in Excel

We can find top **10 **values based on a single criterion using different functions and their combinations. I am showing below 3 ways to calculate the top **10 **values with single criteria.

#### 1.1 Insert Combination of LARGE, IF and ROW Functions

Let’s follow the stepwise procedure given below for using **LARGE**, **IF** and **ROW **functions altogether to get the top **10 **values based on single criteria.

- Firstly, select
**Cell G6**and write the following formula there.

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

- Next, press
**Enter**. - Consecutively, we will see the top
**10**marks of**physics**in the range of cells**G6:G15**.

In the formula,

**ROW(A1:A10)**this part creates an array of numbers**1**to**10**.- And,
**IF(D6:D20>60, C6:C20,””)**this part 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.

The **LARGE **function takes the above parts as arguments and gives the output array consisting top **10 **numbers in **Physics**.

#### 1.2 Apply XLOOKUP, LARGE and FILTER Functions Together

We can also use **XLOOKUP**, **LARGE **and **FILTER **functions together to determine the top **10 **values based on single criteria. Let’s follow the steps given below.

- First, select
**Cell G6**and write the following formula there.

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

- Then, hit
**Enter**. - As a result, we will see the
**Name**of the students who got top**10**numbers in**Physics**.

The formula contanis,

**LARGE(FILTER(C6:C20,D6:D20>60),ROW(A1:A10))**which 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**

#### 1.3 Use INDEX-MATCH Combination

We can do the same task by using the** INDEX **and the** MATCH **functions together. I am showing the steps for the procedures below.

- First, 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)`

- After that, press
**Enter**. - Finally, we will see the
**Name**of students who got**Top 10 Marks in Physics**.

In the formula,

**ROW(A1:A10))**this part gives an array of**1**to**10**.**FILTER(C6:C20,D6:D20>60)**this part 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.

The **MATCH **and the **INDEX **function take all the above parts as arguments and give the **Name **corresponding to top **10 **marks in physics.

### 2. Get Top 10 Values Based on Multiple Criteria in Excel

Instead of one criterion, we can find the top **10 **values that satisfy multiple criteria. For the procedures follow the steps given below to get top **10 **values based on multiple criteria in Excel.

- Start with selecting
**Cell G6**and write the following formula there

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

- Then, hit
**Enter**. - Consecutively, We will see top
**10**marks in physics that satisfy the conditions (more than**50**marks in history and more than**60**marks in chemistry).

In the formula,

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

Finally, The **LARGE **function takes the above parts as arguments and gives the final result as an array.

## More Examples of Finding Top 10 Values in Excel

Now, I will show some examples of finding top **10 **numbers without any criteria.

### 1. Use of the LARGE & ROW Functions to Get Top 10 Numbers

Please follow the given steps for using the **LARGE **and the **ROW **functions to get top **10 **numbers.

- First, select
**Cell G6**and write the following formula there.

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

- Then, hit
**Enter**. - Consecutively, we will see the result consecutively.

**Note:**The formula contains the

**LARGE**and the

**ROW**functions which work as described in the methods before.

### 2. Apply the LARGE & COLUMN Functions for Finding Top 2 Values in a Row

We can change the **ROW **function with the** COLUMN **function to get the top numbers in a row.

- Firstly, select
**Cell G6**and write the following formula there.

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

- Then, hit
**Enter**. - Therefore, we will see the top
**2**numbers of a student’s**Name**.

**Note:**The formula is very much similar to the previous method except that it uses the

**COLUMN**function(works along the row) instead of the

**ROW**function.

### 3. Identify Top 10 Numbers Using the SORT & FILTER Functions

Top **10 **numbers can be found by using the **SORT **and the **FILTER **function. Let’s follow the steps given below for that.

- Firstly, select
**Cell G6**and write the following formula there.

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

- Afterward, press
**Enter**. - Instantly, we will see an array of results.

In the formula,

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

### 4. Utilize INDEX and MATCH Functions to Find Names Which Contain Top 10 Values

We can also find the respective names for the top numbers. We will use the **INDEX **and the **MATCH **functions for the procedures. Let’s follow the steps.

- First, select
**Cell G6**and write the following formula there.

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

- Furthermore, hit
**Enter**. - Consecutively, we will see the results.

In the formula,

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

### 5. Use XLOOKUP & LARGE Functions to Get the Top 10 Numbers

We can also use the **XLOOKUP **and the **LARGE **functions to get the top **10 **numbers without any criteria. Follow the steps given below for this method,

- First, select
**Cell G6**and write the following formula there.

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

- Afterward, hit
**Enter**and we will see the result.

**Note:**The formula uses the

**XLOOKUP**, the

**LARGE**and the

**ROW**functions. We used them before in the previous methods. These functions work in a similar manner to find the top

**10**results.

## How to Identify Top 10 Values with Duplicates in Excel

Sometimes, our dataset may contain repeated values. If we want to find the top **10 **numbers without repetitions, follow the given steps.

- Firstly, select
**Cell G6**and write the following formula there.

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

- Then, hit
**Enter**. - Finally, we will see the results.

In the formula,

**UNIQUE(E6:E20)**this part finds the unique values.- Which in turn acts as an argument of the
**LARGE**function.

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

**Download Practice Workbook**

You can download the practice workbook from here.

## Conclusion

We can find top 10 numbers based on criteria in several ways. In this article, I have shown 2 ideal examples which find the top 10 numbers based on criteria in Excel. If you have any queries or suggestions, please let us know by commenting.

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