Top 10 Values Based on Criteria in Excel (Both Single and Multiple Criteria)

Today I will be showing how you can filter out the top 10 values based on any criteria from any set of data in Microsoft Excel.


How to Find Top n Values in Excel

First of all, let’s see how we can filter out any top n values in Excel. To achieve that, let’s be introduced with our data set first.

We have the students’ record of a school named Sunflower Kindergarten. We have the student names in column B, and their marks in Physics, Chemistry and Literature in columns C, D and E respectively.

A Data Set in Excel

Now, let’s try to sort out the top 10 marks in Physics (Column C).


1. Using LARGE and ROW Functions

We can extract out the top 10 marks in Physics by using a combination of LARGE and ROW functions of Excel.

The formula will be:

=LARGE(C4:C20,ROW(A1:A10))

Top 10 Values Using LARGE and ROW in Excel

See, we have got the top 10 marks in Physics.

Note: It is an Array Formula. So you have to press Ctrl + Shift + Enter to enter the formula and drag the Fill Handle to get all the values (Not necessary in Office 365)

Now, for better understanding, let’s break down the formula.

ROW(A1:A10) returns an array of values from 1 to 10, column-wise.

ROW Function in Excel

So, the formula becomes =LARGE(C4:C20,{1,2,3,4,5,6,7,8,9,10}).

Note: I have used rows A1 to A10 to get rows from 1 to 10. You can use it according to your wish. Like B1:B10, C1:C10, etc. And if you require any other number of rows than 10, use that. For example, to get the top 5 values, you can use A1:A5, B1:B5, and so on.

The LARGE function takes a range of cells and a parameter k as the arguments. And returns the kth large value from that range of cells.

Here, instead of taking a single parameter k, it takes an array of values {1,2,3,4,5,6,7,8,9,10} as k.

So it returns the 1st, 2nd, 3rd, 4th, 5th, 6th, 7th, 8th, 9th, and 10th largest values respectively from the cells C4 to C20.

These are the top 10 marks in Physics that we were looking for.


2. Using LARGE and COLUMN Functions

In the previous section, we filtered out the top n values from a column. Now we shall try to filter out any top n values from a row.

Let’s try to find out the top 2 marks obtained by the first student, James Austin.

Select a cell adjacent to him and enter this formula:

=LARGE($C4:$E4,COLUMN(A1:B1))

Top 2 Values Using LARGE and COLUMN in Excel

See, we have got the top two marks of the first student James Austin.

Note: You have to press Ctrl + Shift + Enter to enter this formula unless you use Office 365.

Now if you wish, you can drag the Fill Handle to get the top 2 marks of all the students.

Dragging Fill Handle in LARGE and COLUMN formula

Now, for sake of understanding, let’s break down the formula.

COLUMN(A1:B1) returns an array of 1 to 2, {1,2}, in row-wise. (To know more, go to the previous section).

COLUMN Function in Excel

The formula then becomes LARGE($C4:$E4,{1,2}). It returns the 1st and 2nd largest values from the cells C4, D4, and E4.

This is what we want.

Notice one thing. We have used mixed cell reference for the lookup values in this formula, $C4:$E4. 

This is because we want Excel to search for the largest values in between just C4, D4 and E4. But if we use C4:E4 in place of $C4:$E4, it will search for the large values from all the cells in between C4 and E4, like C4, C5, C6 and so on.

That is why we locked the columns.

We could have used absolute cell reference$C$4:$E$4. But we did not use that because when we dragged the Fill Handle to get the top 2 marks of the other students, we wanted the row numbers in the formula to increase by one.

That’s why we just locked the columns, not the rows.


3. Using SORT and FILTER Functions

We can also use the SORT and the FILTER functions of Excel to extract out any top n values from a set of data.

But remember, these two functions are available in Office 365 only.

Select any cell and enter this formula:

=FILTER(C4:C20,C4:C20>=LARGE(C4:C20,10))

Top 10 Values Using FILTER Function

See, we have got the top 10 marks in Physics.

Let’s break down the formula now.

LARGE(C4:C20,10) returns the 10th largest mark in Physics, from cell C4 to C20. In this case, it is 64.

LARGE Function in Excel

C4:C20>=LARGE(C4:C20,10) returns an array of TRUE or FALSE for each cell in the range C4 to C20. TRUE if the cell content is greater than or equal to the 10th largest value,68. And FALSE otherwise.

Formula of Criteria in Excel

So the formula now becomes =FILTER(C4:C20,{TRUE,TRUE,...,FALSE,...,TRUE})

FILTER function returns the content of the cell from C4 to C20 when it faces a TRUE, and returns nothing if it faces a FALSE.

This is how we get the top 10 marks in Physics.

Now if you carefully notice, you will find that though we have got the top 10 marks, they are not arranged serially, from high to low.

But no worries, if you want, you can achieve this by using the SORT function of Excel.

Just wrap the formula inside a SORT function:

=SORT(FILTER(C4:C20,C4:C20>=LARGE(C4:C20,10)),1,-1)

SORT Function in Excel

See, we have arranged the top 10 marks in descending order.

Remember, the syntax of the SORT function is:

=SORT (array, [sort_index], [sort_order], [by_col])

We have used a 1 inside the SORT function because this is the column index to sort here.

And a -1 because this is the value to be used to sort in descending order. To sort in the ascending order, use 1 in place of -1.

The [by_col] argument is optional here. It is set by default FALSE. This means we are not sorting by columns here, we are sorting by rows.


4. Getting the Names of the Top 10 Values

Now we have got several ways to filter out the top 10 marks in Physics.

But can you extract out the names of the students who got these top 10 marks in Physics?

Very easy. Just wrap any of the above formulas within an INDEX-MATCH or XLOOKUP function.

First of all, let’s use INDEX-MATCH along with the LARGE and ROW functions.

The formula will be:

=INDEX(B4:B20,MATCH(LARGE(C4:C20,ROW(A1:A10)),C4:C20,0),1)

Names of the Top 10 Values Using INDEX-MATCH

See, we have got the top 10 students in Physics.

Note: It is an Array Formula. Do not forget to press Ctrl + Shift + Enter unless you are in Office 365.

Now let’s break down the formula.

MATCH(LARGE(C4:C20,ROW(A1:A10)),C4:C20,0) returns the row numbers of the students with the top 10 marks between cell C4 and C20.

Row Number of Top 10 Values Using MATCH Function

Now the formula becomes =INDEX(B4:B20,{3,1,12,11,7,2,17,16,6,4},1).

It returns the cell contents of the cells from the table B4 to B20, with row equals to each of the arrays and column equals 1.

These are the required names of the top 10 students in Physics.

If you want, you can accomplish this using the XLOOKUP function, like this:

=XLOOKUP(LARGE(C4:C20,ROW(A1:A10)),C4:C20,B4:B20)

(Do not forget to press Ctrl + Shift + Enter)

Names of the Top 10 Values Using XLOOKUP


Top 10 Values in Excel Based on Criteria

Now we have learned how to filter out any top n values from a set of data.

This time we shall try to figure out the top 10 values from a set of data based on some criteria.


1. Based on Single Criterion

Let’s go for a single criterion first.

Can you figure out the top 10 marks in Physics where the marks in Chemistry are also greater than 70?

Pretty easy. Use this formula:

=LARGE(IF(D4:D20>60,C4:C20,""),ROW(A1:A10))

Top 10 Values Based on Single Criterion

See, we have got the top 10 marks in Physics with marks in Chemistry also greater than 60.

[Do not forget to press Ctrl + Shift + Enter.]

Now let’s break down the formula.

ROW(A1:A10) returns an array of values from 1 to 10 column-wise, {1,2,3,4,5,6,7,8,9,10}.

(Details in the earlier sections)

D4:D20>60 returns an array of TRUE or FALSE, TRUE if the mark in Chemistry is greater than 60, FALSE otherwise.

A Formula Having Condition in Excel

IF(D4:D20>60,C4:C20,””) now becomes IF({FALSE,TRUE,TRUE,…,TRUE},{C4,C5,…,C20},””)

It returns the adjacent cell from column C (marks in Physics) for a TRUE, and returns nothing for a FALSE.

IF Formula in Excel

The complete formula now becomes LARGE({75,98,..,72},{1,2,3,...,10})

It returns the 1st, 2nd, 3rd, 4th, 5th, 6th, 7th, 8th, 9th and 10th largest values from the array {75,98,..,72}.

These are what we want.

If you have Office 365 access, you can achieve the same goal using the FILTER function in place of IF.

=LARGE(FILTER(C4:C20,D4:D20>60),ROW(A1:A10))

Top 10 Values Based on Single Criterion Using FILTER Function

And to get the names of the top 10 students in Physics, who also got more than 60 in Chemistry, wrap any of the two formulas inside an XLOOKUP or INDEX-MATCH, like this:

=XLOOKUP(LARGE(FILTER(C4:C20,D4:D20>60),ROW(A1:A10)),C4:C20,B4:B20)

Names of the Top 10 Values Based on Criteria Using XLOOKUP

And if you wish, you can use the INDEX-MATCH formula too.

=INDEX(B4:B20,MATCH(LARGE(FILTER(C4:C20,D4:D20>60),ROW(A1:A10)),C4:C20,B4:B20,C4:C20,0),1)

Whatever you use, do not forget to press Ctrl + Shift + Enter.


2. Based on Multiple Criteria

Now comes multiple criteria.

Let’s try to find out the top 10 marks in Physics where also marks in Chemistry are greater than 60  and marks in Literature are greater than 50.

Don’t worry. Just multiply the two criteria with keeping “–” in front in place of the single criteria within the IF function of the previous section:

=LARGE(IF((--D4:D20>60)*(--E4:E20>50),C4:C20,""),ROW(A1:A10))

=LARGE(IF((--D4:D20>60)*(--E4:E20>50),C4:C20,""),ROW(A1:A10))

Here (--D4:D20>60) returns a 1 if the mark in Chemistry is greater than 60, otherwise 0.

Similarly, (--E4:E20>50) returns a 1 if the mark in Literature is greater than 50, otherwise 0.

So, (--D4:D20>60)*(--E4:E20>50) returns a 1 if both the conditions are fulfilled, otherwise it returns a 0.

Two Logical Formulas Multiplied in Excel

The rest is the same as the previous section.

Note: You can use more than two criteria in this way.


Top 10 Values with Duplicates

Let’s accomplish our last task today.

If you have a close look at the marks in Literature, you will see some marks have duplicates.

That is, 88 appears twice, 71 appears thrice, and so on.

If we use any of the formulas discussed above to filter out the top 10 values, the duplicate values will appear multiple times according to their numbers of appearances.

But we want each value to appear only once, despite having duplicates.

How to achieve that?

There are ways.

You can use the formula mentioned below to filter out the top 10 marks in Literature including the duplicate values only once.

=LARGE(UNIQUE(E4:E20),ROW(A1:A10))

Top 10 Values with Duplicates in Excel

See, we have got the top 10 marks with the duplicates only once.

Here E4:E20 is the range of data where I am searching for. You use your one.


Conclusion

Using these methods, we can filter out the top 10 values based on any criteria from any set of data in Excel. Do you know any other method? Let us know in the comment section.

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

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo