In this tutorial, I am going to show you 4 quick tricks to find the second largest value with criteria in Excel. You can use these methods even in large datasets to find out the second largest value by setting criteria. Throughout this tutorial, you will also learn some important Excel tools and techniques that will be very useful in any Excel-related task.

**Table of Contents**Expand

## How to Find Second Largest Value with Criteria In Excel: 4 Quick Tricks

We have taken a concise dataset to explain the steps clearly. The dataset has approximately 7 rows and 3 columns. Initially, we are keeping all the cells in *Currency* format. For all the datasets, we have 2 unique columns which are *Club Name, Home Kit, *and *Away Kit*. Although we may vary the number of columns later on if that is needed.

### 1. Using LARGE Function

The **LARGE** function can return the largest number from a list of numbers after we sorted it in descending order. Let us see how to apply this function to find the second largest value with criteria.

**Steps:**

- Go to cell
**F7**and insert the following formula.

`=LARGE(IF(C4:D4=F5,C5:D10),2)`

- Press
**Enter**and this will calculate the second largest*Home Kit*price in cell**F7**.

**ðŸ”Ž ****How Does the Formula Work?**

**IF(C4:D4=F5,C5:D10):**This portion returns an array of the cell values and*FALSE*cell values.**=LARGE(IF(C4:D4=F5,C5:D10),2):Â**This part of the formula returns the final value of*119*.

**Read More: **How to Find Largest Number in Excel

### 2. Applying AGGREGATE Function

The **AGGREGATE** function gives us the ability to perform aggregate calculations like **COUNT**, **AVERAGE**, **MAX**, etc. This function also ignores any hidden rows or errors. We will use this function to find the second-largest value with specific criteria.

**Steps:**

- Double-click on cell
**F7**and enter the below formula.

`=AGGREGATE(14,6,(C5:D10)/(C4:D4=F5),2)`

- Next, press the
**Enter**key and you should get the second-largest away kit price.

### 3. Utilizing SUMPRODUCT Function

The **SUMPRODUCT** function first multiplies the range of values and then gives the sum of those multiplications. We can use this function along with the **LARGE **function to find the second largest value with criteria.

**Steps:**

- To begin this method, double-click on cell
**F7**and insert the formula below.

`=SUMPRODUCT(LARGE((B5:B10=F5)*(C5:D10),2))`

- Next, press the
**Enter**key and you will find the second largest price value for the*Man City*kit in cell**C10**.

**ðŸ”Ž ****How Does the Formula Work?**

**(B5:B10=F5)*(C5:D10):**This portion of the formula returns an array of values that are the highest in the list and other values as 0.**LARGE((B5:B10=F5)*(C5:D10),2):**This portion gives the value 80 as the second largest value.**=SUMPRODUCT(LARGE((B5:B10=F5)*(C5:D10),2)):**This part gives back the final value which is 80 in this case.Â

**Read More:** How to Lookup Next Largest Value in Excel

### 4. Using VBA Code

If you are familiar with **VBA **in Excel, you can find the second-largest value with criteria with just a few clicks. Let us see how to do this.

**Steps:**

- For this method, go to the
**Developer**tab and select**Visual Basic**.

- Now, select
**Insert**in the**VBA**window and click on**Module**.

- Next, type in the formula below in the new window.

```
Sub SecondLargest()
Range("F7").FormulaArray = "=LARGE(IF(C4:D4=F5,C5:D12),2)"
End Sub
```

- Open the macros from the
**Developer**tab by clicking on**Macros**.

- From the
**Macro**window, select the macro named*SecondLargest*and click**Run**.

- The VBA code will calculate the second-highest value from all the away kits in cell
**F7**.

**Read More:Â **How to Use **VBA Large** Function in Excel

## How to Find Top 5 Values and Names with Criteria in Excel

In this method, we will see the detailed steps to find the top 5 values and names with criteria in Excel.

**Steps:**

- Move to cell
**E5**and enter the below formula.

`=LARGE($C$5:$C$10,ROWS($E$5:$E5))`

- Press the
**Enter**key and copy this formula to the cells using the**Fill Handle**. - This will find the top 5 values for the home kits.

**ðŸ”Ž ****How Does the Formula Work?**

**ROWS($E$5:$E5):**This portion gives the value of 1.**=LARGE($C$5:$C$10,ROWS($E$5:$E5)):Â**This portion returns the final value which is the top 5 home kit prices.

## Things to Remember

- You can use the
**ALT + F11**shortcut to open the**VBA**window and**ALT + F8**to open the**Macros**window. - Note that the
**LARGE**function ignores cells that are empty or contain*TRUE*or*FALSE*values in them. - If there is no numeric value, this function might return the
*#NUM!*error as a result.

**Download Practice Workbook**

You can download the practice workbook from here.

## Conclusion

I hope that you will be able to apply the methods that I showed in this tutorial on how to find the second-largest value with criteria in Excel. As you can see, there are quite a few ways to achieve this. So wisely choose the method that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more Excel techniques, follow ourÂ website. If you have any queries, please let me know in the comments.

## Related Articles

- How to Use Excel LARGE Function with Criteria
- How to Use Excel LARGE Function with Duplicates in Excel
- How to Use Excel Large Function with Text
- How to Use LARGE Function with VLOOKUP Function in Excel
- How to Use Excel Large Function in Multiple Ranges
- How to Use LARGE and SMALL Function in Excel

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

I have a range of cells containing,

12,

12,

12,

12,

11,

10,

9,

1,

0.

I want a return of second highest row2 (12) in formula

Hi

D V V NARAYANARAJU,Thank you very much for reading our articles.

In your query, you wanted to know how to return the second highest value which is

12located at row2using the formula.You can use the following formula:

`=LARGE($B$5:$B$13,2)`

We have inserted the values in the

range B5:B13.In return, we get

12which indicatescell B6which is the2ndrow of the range.If you have further queries regarding this topic, then inform us.

Regards

Alok

ExcelDemy