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.


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.

excel top 10 based on criteria

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

Get Top 10 Values Based on Multiple Criteria in Excel

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.

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

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.

Identify Top 10 Numbers Using the SORT & FILTER Functions

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.

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

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.

How to Identify Top 10 Values with Duplicates in Excel

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

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.

4 Comments
  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!

Leave a reply

ExcelDemy
Logo