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.

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

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

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

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

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

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

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

**Read More: **How to Use Excel **LARGE** Function with Duplicates 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:**

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

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

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

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

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

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

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

## Related Articles

- How to Use Excel Large Function with Text
- How to Lookup Next Largest Value in Excel

- How to Use LARGE Function with VLOOKUP Function in Excel
- How to Use VBA Large Function in Excel
- How to Use LARGE and SMALL Function in Excel

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