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.

## Download Practice Workbook

You can download the practice workbook from here.

## 2 Ideal Examples of Getting Top 10 Values Based on Criteria

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

**Read More:** **How to Get Cell Value by Row and Column in Excel VBA**

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

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

**Read More:** **How to Find Highest Value in Excel Column (4 Methods)**

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

**Read More:**** How to Find Value in Column Using VBA in Excel (4 Ways)**

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

**Read More:** **How to Find Top 5 Values and Names in Excel (8 Useful Ways)**

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

**Read More:**** Lookup Value in Column and Return Value of Another Column in Excel**

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

**Read More:** **How to Find Last Occurrence of a Value in a Column in Excel (5 Methods)**

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

**Read More:** **How to Find Value in Column in Excel (4 Methods)**

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

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

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