How to Find Top 10 Values Based on Criteria in Excel

To find the top 10 values based on criteria in Excel, you can use a combination of the LARGE, IF, and ROW functions. Follow these steps to find the top 10 values based on your criteria:

1. Prepare your data: Make sure your data is well-organized, with headers and clearly defined criteria.
2. Add a helper column: Add a new column next to your data to help identify the rows that meet your criteria.
3. Use the IF function: In the helper column, use the IF function to check if the criteria are met. If the criteria are met, return the row number; otherwise, return a blank cell.
4. Use the LARGE function: In a separate cell, use the LARGE function to find the top 10 values in the helper column.
5. Retrieve the top 10 values: Use the INDEX and MATCH functions to retrieve the corresponding values in your data based on the top 10 row numbers found in step 4.

Here's an example to demonstrate the process:

Example

Assume you have the following sales data and you want to find the top 10 sales values for product "A":

A	        B	      C
1	Product	Sales
2	A	    100
3	B	    200
4	A	    300
5	C	    400
6	A	    500
7	B	    600
8	A	    700
9	C	    800
10	A	    900

Follow these steps:

1. Add a helper column (D) next to your data.
2. In cell D2, enter the following formula:

=IF(A2="A",ROW(),"")

This formula checks if the product in column A is "A" and returns the row number if it's true.

3. Copy this formula down the helper column.
4. In a separate cell (E1), enter the following formula to find the top value (the first highest value) in the helper column:

=LARGE(D:D,1)

5. In cell F1, enter the following formula to retrieve the corresponding sales value for the top row number found in step 4:

=INDEX(B:B,MATCH(E1,D:D,0))

6. Repeat steps 4 and 5 for the top 2 to top 10 values, changing the number 1 in the LARGE formula to 2, 3, 4, and so on.
7. Finally, you will get the top 10 sales values for product "A" in column F.

Note: If you have more advanced criteria or need to work with Excel Tables or dynamic arrays, consider using the FILTER and SORT functions available in Excel 365.