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