How to Use Excel LARGE Function with Criteria (4 Suitable Ways)

In this article, we will learn how to use the LARGE function with criteria in Excel. With the LARGE function, you can get the maximum number from a range. Inserting criteria within this function, we can do some incredible tasks in Excel. So, without any delay, let’s start the discussion.

How to Use Excel LARGE Function with Criteria: 4 Suitable Ways

To demonstrate the usage of the LARGE function with criteria in Excel, we will use the following dataset. The dataset represents the marks of 2 sections (Section A and Section B) of class 7. Here, marks in Science and English are given. Now, we will show 4 suitable ways of using the Excel LARGE function with criteria.

1. Use Excel LARGE Function with Single Criteria

We can combine the IF function with the LARGE function to use the formula for single criteria.

STEPS:

We will find out the highest mark in English from the combined dataset of sections A and B.
Write down the following formula in cell G6.

=LARGE(IF(D5:D14=G5,E5:E14),1)

Then, press Enter and you can see the result.
From the dataset, the highest mark in English is 96.

Here, the IF function checks the condition and the Large function finds out the largest number from the extracted data from the IF function.

2. Apply Multiple Criteria for Excel LARGE Function

Previously, we have shown how to apply the Excel LARGE function for single criteria. You can also apply multiple criteria in the LARGE function. Using OR and AND logic, you can easily complete these tasks.

2.1. Using OR Logic to Insert Multiple Criteria

Suppose, you want to find out the highest mark for English or Science. Or, you wish to find out the highest mark in English or A section. Then, by using OR logic, you can easily apply multiple criteria for the Excel LARGE function.

STEPS:

Here, we want to find out the top score in English or section A.
So, write down the following formula in cell G7.

=LARGE(IF((D5:D14=G5) +(C5:C14=H5), E5:E14), 1)

Press Enter and you can see the result.
From the dataset, the highest mark in English is 96 and in section A is 88.
As 96 is greater than 88, the result is showing 96.

Here, the IF function checks the conditions and extracts the numbers fulfilling at least 1 condition. After that, the LARGE function finds the largest number from the extracted data from the IF function.

We want to find out the top score in Science or B section.
Type Science and B in the G5 and H5 cells respectively.
After typing, press Enter to proceed.
Instantly, you can see the result is 95.

2.2. Using AND Logic to Place Multiple Criteria

Suppose, you want to find out the highest mark for the students of section C for Science. Then, you have to use AND logic. Here, you need to consider both criteria.

STEPS:

Here, we want to find out the top score in section C for the subject of Science.
So, write down the following formula in cell G7.

=LARGE(IF((D5:D14=G5) * (C5:C14=H5), E5:E14), 1)

Then, press Enter and you can see the result.
From the dataset, the highest mark in Science is 95 but it is in the B section.
And, the highest mark in section C is 96 but the subject is English.
Within the C section, the highest mark in Science is 71.
That’s why the result is showing 71.

Here, the IF function checks the conditions and extracts the numbers fulfilling both conditions. After that, the LARGE function finds the largest number from the extracted data from the IF function.

Now, we want to find out the top score in Science within section A.
Type A in cell H5.
After typing, press Enter to proceed.
But, there is no Science subject in section A.
For this reason, the result is showing an Error.

After that, we will find out the top score in English within section A.
So, type English in the G5 cell.
After typing, press Enter to proceed
Instantly, you can see the result is 88.

3. Filter Top N Values Using LARGE Function Based on Criteria

In Excel, you can easily filter top N values based on both single and multiple criteria. Here, we will show you 3 examples of this type. Let’s follow us to filter top N values using the LARGE function based on criteria.

3.1 Applying Single Criteria Using SORT, FILTER, IF, and LARGE Functions

Using the SORT, FILTER, IF, and LARGE functions, we can build up a formula. Using this formula, we will filter out the top 3 marks based on a single criterion.

STEPS:

Here, we want to find out the top 3 scores in Science.
Write down the following formula in cell G6.

=SORT(FILTER(E5:E14, (E5:E14 >=LARGE(IF(D5:D14=G5, E5:E14), 2))

Then, press Enter and you can see the top 3 marks in Science.

Here, the FILTER function filters the array of data based on criteria. With the LARGE function, we can find out the 1st, 2nd, and 3rd highest marks and the SORT function sorts the data sequentially.

We want to find out the top 3 scores in English.
Type Science in cell G5.
After typing, press Enter to proceed.
You can see the top 3 marks in English.
As 79 has come out double times, the result is showing two 79.

In the following step, we want to make some modifications.
We want to find out the top 2 scores in English.
To do so, we need to just replace 2 with 3 in the previous formula.
Therefore, the formula will be.

=SORT(FILTER(E5:E14, (E5:E14 >=LARGE(IF(D5:D14=G5, E5:E14), 2)) * (D5:D14=G5)), 1, -1)

Then, press Enter and you can see the top 2 marks in English.

3.2 Combining Multiple Criteria Applying SORT, FILTER, IF, and LARGE Functions Using OR Logic

Using the SORT, FILTER, IF, and LARGE functions, we can build up another formula. From this formula, we can filter out the top N marks based on multiple criteria. Here, we will extract the information of the top N values from the dataset if any one criterion is fulfilled.

STEPS:

In this step, we want to find out the top 2 scores of section C or Science.
Write down the following formula in cell G6.

=SORT(FILTER(B5:E14, (E5:E14>=LARGE(IF((C5:C14=H5)+(D5:D14=G5), E5:E14), 2))* ((C5:C14=H5)+(D5:D14=G5))), 2, -1)

Then, press Enter and you can see the top 2 marks of section C or Science.
Here, 1st is Emma where the condition of section C is fulfilled.
And, 2nd is Raechel where the condition of the subject Science is fulfilled.

With the LARGE function, we can find out the 1st and 2nd highest marks and the FILTER function filters the data from the dataset. Finally, the SORT function sorts the data sequentially.

Similarly, you can find out the top 2 scores of section A or English.

3.3 Integrating Multiple Criteria of SORT, FILTER, IF, and LARGE Functions Employing AND Logic

By integrating the SORT, FILTER, IF, and LARGE functions, we can build up another formula. From this formula, we can filter out the top N marks based on multiple criteria. Here, we will extract the information of the top N values from the dataset if both criteria are fulfilled.

STEPS:

In this step, we want to find out the top 3 scores in section C for the subject of Science.
So, write down the following formula in cell G6.

=SORT(FILTER(B5:E14, (E5:E14>=LARGE(IF((C5:C14=H5)*(D5:D14=G5), E5:E14), 2)) * ((C5:C14=H5)*(D5:D14=G5))), 2, -1)

Press Enter and you can see the top 2 scores in section C for the subject of Science.

Here, with the LARGE function, we can find out the 1st and 2nd highest marks and the FILTER function filters the data from the dataset. Finally, the SORT function sorts the data sequentially.

Similarly, you can find the top 2 scores in Section A for the subject of English.

4. Filter Nth Largest Value Using LARGE Function With Criteria

We can combine the IF and LARGE functions to determine the Nth largest value. We can apply single or multiple criteria inside the LARGE function.

4.1 Applying Single Criteria to Find Nth Largest Value

Here, we will find out the 3rd highest mark in English from the combined dataset of sections A and B.

STEPS:

Write down the following formula in cell G6.

=LARGE(FILTER(E5:E14,D5:D14=G5),3)

Press Enter and you can see the result.
From the dataset, the 3rd highest mark in English is 79.

Here, the IF function checks the condition and the LARGE function finds out 3rd largest number from the extracted data from the IF function. If you inserted 2 instead of 3 in the formula, then you can get the 2nd largest number.

Similarly, you can find out the 3rd largest mark in Science.
To do so, type Science in cell G5 and press Enter.
As a result, you can see the result is 71.

4.2 Using OR Logic to Get Nth Largest Value

Here, we can filter out the Nth highest mark based on multiple criteria. If we use OR logic, then we will get the mark of Nth highest value from the dataset depending on fulfilling any one criterion.

STEPS:

Here, we will find out the 2nd highest mark of the combined dataset of Science and Section A.
Write down the following formula in cell G6.

=LARGE(IF((D5:D14=G5) +(C5:C14=H5), E5:E14), 2)

Then, press Enter and you can see the result.
From the dataset, the 2nd highest mark of the combined dataset of Science and section A is 88.

Here, the IF function checks the conditions and extracts the numbers fulfilling at least 1 condition. After that, the LARGE function finds the 2nd largest number from the extracted data from the IF function.

4.3 Calculate Nth Largest Value Utilizing AND Logic

If we use AND logic, then we will get the mark of Nth highest value from the dataset depending on fulfilling both criteria.

STEPS:

Here, we will find out the 2nd highest mark from the students of section C for English.
So, write down the following formula in cell G6.

=LARGE(IF((D5:D14=G5) *(C5:C14=H5), E5:E14), 2)

Then, press Enter and you can see the result.
From the dataset, the 2nd highest mark from the students of section C for Science is 79.

Here, the IF function checks the conditions and extracts the numbers fulfilling both conditions. After that, the LARGE function finds the 3rd largest number from the extracted data from the IF function.

Practice Section

There is a practice Excel Sheet available. You can practice from this sheet.

Download Practice Workbook

To practice by yourself, download the following workbook.

Large Function With Criteria.xlsx

Conclusion

In this article, we have demonstrated how to use the LARGE function with criteria in Excel. There is a practice workbook at the beginning of the article. Go ahead and give it a try. To read similar articles, check out our website. Last but not least, please use the comment section below to post any questions or make any suggestions you might have.

<< Go Back to Excel LARGE Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

How to Use Excel LARGE Function with Criteria (4 Suitable Ways)

How to Use Excel LARGE Function with Criteria: 4 Suitable Ways

1. Use Excel LARGE Function with Single Criteria

2. Apply Multiple Criteria for Excel LARGE Function

2.1. Using OR Logic to Insert Multiple Criteria

2.2. Using AND Logic to Place Multiple Criteria

3. Filter Top N Values Using LARGE Function Based on Criteria

3.1 Applying Single Criteria Using SORT, FILTER, IF, and LARGE Functions

3.2 Combining Multiple Criteria Applying SORT, FILTER, IF, and LARGE Functions Using OR Logic

3.3 Integrating Multiple Criteria of SORT, FILTER, IF, and LARGE Functions Employing AND Logic

4. Filter Nth Largest Value Using LARGE Function With Criteria

4.1 Applying Single Criteria to Find Nth Largest Value

4.2 Using OR Logic to Get Nth Largest Value

4.3 Calculate Nth Largest Value Utilizing AND Logic

Practice Section

Conclusion

Related Articles

Leave a reply Cancel reply