# How to Get Top 10 Values Based on Criteria in Excel

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.

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

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

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

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

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

1. Reply Great article. Congratulations
Many thanks

• Reply Hi Luis,
Thank you for your response. Hope this comes in handy for more people like you.

2. Reply 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 Hi Nico,    