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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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