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

In this article, we will learn how to use 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.

excel large function with criteria


Download Practice Workbook

To practice by yourself, download the following workbook.


4 Suitable Ways of Using Excel LARGE Function with Criteria

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 Excel LARGE Function with criteria.

Sample Dataset  to Use  LARGE Function with Criteria in Excel


1. Use Excel LARGE Function with Single Criteria

We can combine the IF Function and the LARGE Function to use the formula for single criteria. It is a very easy step. You just need to insert a simple formula.

STEPS:

  • Here, we will find out the highest mark in English from the combined dataset of sections A and B.
  • So, write down the following formula in the G6 cell:
=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.

Formula for Using Excel Large Function with Single Criteria

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

Read More: How to Use VBA Large Function in Excel (4 Suitable Examples)


2. Apply Multiple Criteria for Excel LARGE Function

Previously, we have shown how to apply 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 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 the G7 cell:
=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 English is 96 and in Section A is 88.
  • As 96 is greater than 88, the result is showing 96.

Formula for Applying OR logic to Insert Multiple Criteria in Excel LARGE  Function

Here, the IF function checks the conditions and extracts the numbers fulfilling at least 1 condition. After that, the LARGE function finds out the largest number from the extracted data from the IF function.
  • Now, we want to find out the top score in Science or Section B.
  • So, 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.

Result of Using OR Logic to Insert Multiple Criteria

Read More: How to Find Largest Lookup Value in Excel (3 Easy Ways)


2.2. Utilizing 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 the G7 cell:
=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 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.

Formula for Applying AND logic to Insert Multiple Criteria

Here, the IF function checks the conditions and extracts the numbers fulfilling both conditions. After that, the LARGE function finds out 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.
  • So, type A in the H5 cell respectively.
  • After typing, press Enter to proceed.
  • But, there is no Science subject in section A.
  • For this reason, the result is showing an Error.

Result of Using AND Logic to Insert Multiple Criteria

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

Result of Using AND Logic to Insert Multiple Criteria in Excel

Read More: How to Use Excel Large Function in Multiple Ranges


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. From 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.
  • So, write down the following formula in the G6 cell:
=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.

Formula for Applying Single Criteria Using SORT, FILTER, IF, and LARGE Functions in Excel

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.
  • Now, we want to find out the top 3 scores in English.
  • So, type Science in the G5 cell.
  • After typing, press Enter to proceed.
  • Instantly, you can see the top 3 marks in English.
  • As 79 has come out double times, the result is showing two 79.

Result of Filtering Top 2 Values Based on single Criteria

  • In the following step, we want to do some modifications.
  • Now, 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.

Result of Filtering Top 2 Values Using LARGE Function Based on Single Criteria

Read More: How to Use LARGE and SMALL Function in Excel


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.
  • So, write down the following formula in the G6 cell:
=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.

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

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 out the top 2 scores of Section A or English.

Result of Filtering Top 2 Values Based on Multiple Criteria with OR Logic in Excel


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 the G6 cell:

=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 scores in Section C for the subject of Science.

Formula for Combining Multiple Criteria Applying SORT, FILTER, IF, and LARGE Functions Using AND Logic

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.

Result of Filtering Top 2 Values Using LARGE Function Based on multiple Criteria with AND Logic

Read More: [Fixed!] Sort Largest to Smallest Not Working in Excel


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:

  • So, write down the following formula in the G6 cell:
=LARGE(FILTER(E5:E14,D5:D14=G5),3)
  • Then, press Enter and you can see the result.
  • From the dataset, the 3rd highest mark in English is 79.

Formula for Filtering 3rd Largest Value Using LARGE Function With Single Criteria

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 the G5 cell and press Enter.
  • As a result, you can see the result is 71.

Formula for Filtering 2nd Largest Value Using LARGE Function With Single Criteria


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:

  • In this step, we will find out the 2nd highest mark of the combined dataset of Science and Section A.
  • So, write down the following formula in the G6 cell:
=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.

Formula for Filtering 2nd Largest Value Using Multiple Criteria Combining with OR Logic

Here, the IF function checks the conditions and extracts the numbers fulfilling at least 1 condition. After that, the LARGE function finds out 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 the G6 cell:
=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.

Formula for Filtering 2nd Largest Value Using LARGE Function With Multiple Criteria Combining with AND Logic

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

Read More: How to Find Second Largest Value with Criteria In Excel


Practice Section

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

Practise Workbook For Excel LARGE Function with Criteria


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 the ExcelDemy website. Last but not least, please use the comment section below to post any questions or make any suggestions you might have.


Related Articles

Sudipta Chandra Sarker

Sudipta Chandra Sarker

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel. Here I will be posting articles related to this. My educational degree is BSc in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology, Bangladesh. I have a great interest in research and development. I always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo