4 Ways to Find Second Largest Value with Criteria in Excel

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.

excel find second largest value with criteria


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)

Agregate function to find second largest value with criteria

๐Ÿ”จ 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)

Large function to find second largest value with criteria

๐Ÿ”จ 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))

Sumproduct function to find second largest value with criteria

๐Ÿ”จ 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.

Macro to find second largest value with criteria

  • 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

(Visited 123 times, 1 visits today)
Priti

Priti

Hello! Welcome to my Profile. I am Priti. I'm now working and researching Microsoft Excel, and I'll share articles on it here. I am passionate about problem-solving, and I hope these articles will help you. Please leave a remark if you have any queries, and I will do my best to find an appropriate answer.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo