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.
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.
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.
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.
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
Get FREE Advanced Excel Exercises with Solutions!
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 SORT and FILTER functions along with the LARGE function 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