In Excel, we can find the largest value or the lowest value easily. However, sometimes we need to find the second-largest value. So, in this article, we demonstrated four easy ways to find the second largest value with criteria in Excel.

**📁 Download Excel File**

You can download the Excel file used for the demonstration from the link below.

## Learn to Find Second Largest Value with Criteria in Excel Using These 4 Unique Methods

Firstly, let us get acquainted with our dataset used throughout the article as an example. We have the names of the students in the first column, and in the second and third columns, the number they have gotten in the Geography and Language test. Here we want to find the second highest number in one of the two subjects. Hence, we will use this dataset to demonstrate four ways to find the second-largest value with criteria in Excel.

**Method 1**

**1. Using AGGREGATE Function**

**The AGGREGATE function** can apply several aggregate functions on a list or database and returns an aggregate with the option to exclude hidden rows and error values. We will use this function to find the second largest value in our dataset with criteria. Let us go through the steps to apply this function.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, select the cell where you want the desired result. Here, we are selecting cell
**G6**.

- Next, insert the formula.

**=AGGREGATE(14,6,($C$6:$D$10)/($C$5:$D$5=F6),2)**

**🔨 Formula Breakdown**

Here, the formula we inserted in cell **G6** is:

**AGGREGATE(14,6,($C$6:$D$10)/($C$5:$D$5=F6),2)**

`👉`

In the** AGGREGATE function, function **14 corresponds to the function **LARGE**.

`👉`

Then option no 6 corresponds to **Ignore Error** values.

`👉`

**($C$6:$D$10)/($C$5:$D$5=F6)**, this part is the first numeric argument for which we want the aggregate value.

`👉`

Lastly, 2 says that we want the second-largest value from the previous argument.

- Lastly, press
**Enter**and we will have the second-largest value for the subject Geography.

**Method 2**

**2. Employing LARGE Function**

We will use the **LARGE function** and **IF function** in this second method. The **LARGE** function returns the n-th largest value in a data set and the **IF **function makes a logical comparison between your desired value and the given value. So, let us see the steps to apply this function to find the second-largest value.

**⬇️⬇️ STEPS ⬇️⬇️**

- We will try to find out the second largest value for the subject
**Language**. - First, select cell
**G6**and insert the formula.

**=LARGE(IF(C5:D5=F6,C6:D10),2)**

**🔨 Formula Breakdown**

Here, the formula inserted in **G6** is:

**LARGE(IF(C5:D5=F6,C6:D10),2)**

👉 IF function compares value in cell **F6** with the range** C5:D5 **and then returns to array **C6:D10 **if **TRUE**.

👉Then, the **LARGE** function displays the second largest value from the returned value of the **IF** function.

- Lastly, press
**Enter**and we will get the second largest value for Language.

**Method 3**

**3. Combining SUMPRODUCT and LARGE Functions**

Now, we will apply **SUMPRODUCT** and **LARGE** functions to find the second largest value. The **SUMPRODUCT **function in Excel typically returns the sum of the products of associated arrays or ranges and does multiplication by default. Though, it can also operate addition, subtraction, and division. On the other hand, the **LARGE** function finds the n-th largest value.

Let us see the steps now.

**⬇️⬇️ STEPS ⬇️⬇️**

- At first, select cell
**G6**and insert the formula.

**=SUMPRODUCT(LARGE((C5:D5=F6)*(C6:D10),2))**

**🔨 Formula Breakdown**

Here, the formula inserted in **G6** is:

**SUMPRODUCT(LARGE((C5:D5=F6)*(C6:D10),2))**

👉**(C5:D5=F6)*(C6:D10)**, in this part if the cell **F6 **equals the range **C5:D5** then it multiplies with the value from range **C6:D10**.

👉Then, the **LARGE **function finds the second largest value from the multiplied values.

👉Lastly, the **SUMPRODUCT** function returns the sum of the products.

- Finally, press
**Enter**and we will have our result.

**Method 4**

**4. Embedding VBA**

We can fill down to find the second largest value with criteria by embedding **Microsoft Visual Basic Application (VBA)** in our workbook. But first, you need to enable the **Developer **tab on your ribbon. Click here to **enable the Developer tab on your ribbon**. Now, let us see the steps.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, go to the
**Developer**tab and select**Visual Basic**from the code group.

- Secondly,
**Microsoft Visual Basic for Application**named box will appear. After that, go to the**Insert**tab and select the Module.

- Then, enter the code and press
**F5**.

```
Sub SecondLargestValue()
Range("G6").FormulaArray = "=LARGE(IF(C5:D5=F6,C6:D10),2)"
End Sub
```

- After that, to run the code, press
**Alt+F8**, select**SecondLargestValue,**and then**Run**.

- Lastly, we will get our desired outcome.

## 📄 Important Notes

`🖊️`

To apply **VBA **codes, ensure you have the Developer tab in the ribbon before applying.

`🖊️`

If no numeric value is provided, this function may return the** #NUM! **Error.

## 📝 Takeaways from This Article

`📌`

This article demonstrated four methods to find the second largest value with criteria in excel.

`📌`

In the first method, we showed the **AGGREGATE **function to find the second largest value with criteria in Excel.

`📌 `

Then, we demonstrated a function that applies **LARGE **functions for finding the second largest value with criteria in Excel.

`📌`

After that, we combined the **LARGE **and** SUMPRODUCT **functions to find the second-largest value.

`📌`

Finally, we embedded **VBA **code to find the second largest value with criteria.

## Conclusion

This article has demonstrated three functions to find the second largest value with criteria in Excel. All these formulas are incredibly effective and simple to use. We hope this article helps you. Please leave a remark if you have any queries. The author will do their best to find an appropriate answer.

For more guides like this, visit **Excelden.com**