Table of Contents
To find the top 10 values based on criteria in Excel, you can use the LARGE function. This function allows you to compare a range of values and return the nth largest value, where n is the number of values to be returned. The LARGE function can be used in combination with other functions, such as IF or SUMIF, to apply criteria to the results. Alternately, you can use the Filter tool in the Data tab to quickly find the top 10 values based on criteria.
You can use the following formulas to find the top 10 values in Excel based on criteria:
Method 1: Find Top 10 Based on One Criteria
=LARGE(IF(A2:A20="Value",C2:C20,""),ROW(A1:A10))
This formula finds the top 10 values in the range C2:C20 where the value in the range A2:A20 is equal to “Value.”
Method 2: Find Top 10 Based on Multiple Criteria
=LARGE(IF((A2:A20="Value")*(--B2:B20>10),C2:C20,""),ROW(A1:A10))
This formula finds the top 10 values in the range C2:C20 where the value in the range A2:A20 is equal to “Value” and the value in the range B2:B20 is greater than 10.
The following examples show how to use each formula in practice.
Example 1: Find Top 10 Values Based on One Criteria
We can use the following formula to find the top 10 values in the Points column where the value in the Team column is equal to “Mavs”:
=LARGE(IF(A2:A20="Mavs",C2:C20,""),ROW(A1:A10))
Note: Make sure you press Ctrl+Shift+Enter after typing this formula.
The following screenshot shows how to use this formula in practice:
Column E shows the top 10 values from the Points column where the the Team column is equal to “Mavs.”
Example 2: Find Top 10 Values Based on Multiple Criteria
We can use the following formula to find the top 10 values in the Points column where the value in the Team column is equal to “Mavs” and the value in the Rebounds column is greater than 6:
=LARGE(IF((A2:A20="Mavs")*(--B2:B20>6),C2:C20,""),ROW(A1:A10))
Note: Make sure you press Ctrl+Shift+Enter after typing this formula.
The following screenshot shows how to use this formula in practice:
Column E shows the top 10 values from the Points column where the the Team column is equal to “Mavs” and the “Rebounds” column is greater than 6.