For different purposes, you may need to find the second largest value in Excel. Today we are going to show you how to find the second largest value with criteria. For conducting the session, we are using Excel 2019. You can choose your preferred version.
Before diving into the session, let’s get to know about today’s workbook which is the base of our examples.
We have a table that consists of the name of the clubs and the price of their three playing kits (Home, Away, Third). Using this table, we will find the second largest value based on criteria.
Note that, this is a basic table with few dummy data. You may encounter a much larger and complex data set in real cases.
You are welcome to download the workbook from the link below.
Find Second Largest Value with Criteria
You can use several functions to find the second largest value based on criteria.
We will show you assuming a scenario, where you are looking for the second-largest price for a club.
Here the club name will be our criteria, based on that, the respective second-highest value will come as our result.
1. Using the LARGE Function
We can use the LARGE function to find out our desired value.
The LARGE function returns numeric values based on their relative standing in a data set.
number_range: The range from where you want to find the kth large value.
k: Specifies the position from the largest value. It is an integer value.
The value k decides which of the values will be returned by the LARGE function. If you want the largest or highest value within a range, then use 1 as the value of k, for the second-highest use 2, and so on.
To know more about the LARGE function, visit the Microsoft Support site.
Seeing the function syntax, you might have assumed that only the LARGE function will not be able to fetch value matching criteria.
Yes, you are right. We can definitely find the second largest value (or any) using the LARGE function, but to check the criteria we need to use another function called IF.
Our generic formula will be
Write the formula in Excel.
Inside the IF function, we have inserted the Club Name column as criteria_range and checked the criteria (Chelsea here) with it.
And as true_value set the number_range. Once the criteria will be found true it starts traversing within this range.
Since we are looking for the second largest value, we set 2 as the value of k in the LARGE function.
Now use CTRL + SHIFT + ENTER to execute the formula. Mere ENTER will not return the appropriate value since it’s an array formula.
We have found the second-largest price for the Chelsea kit.
See another example, let’s change the club from Chelsea.
We have changed the club name (criteria), and it returned the second-highest value for that.
Let’s change the criteria.
In this example, our criterion was Home Kit, we found the second-largest price of Home Kit here.
2. Using AGGREGATE Function
You can find the second-largest value using the AGGREGATE function as well.
The syntax for the AGGREGATE function is as follows
function_number: This number specifies which calculation should be made.
behavior_options: Set this using number. This number denotes how the function will behave.
range: Range you want to aggregate.
The AGGREGATE function does several tasks so numbers of functions are predefined within it. We are listing few frequently used function numbers
From this table, you have understood that we need to use 14 as our function_number.
Once you have chosen 14 (LARGE) as your function, then you need to add another parameter k after the range. So the formula will be
Here k is an integer that specifies the position from the largest value.
There are 8 possible values for behavior_options. They are
|0||Ignore SUBTOTAL and AGGREGATE functions|
|1||Ignore hidden rows, SUBTOTAL and AGGREGATE functions|
|2||Ignore error values, SUBTOTAL and AGGREGATE functions|
|3||Ignore hidden rows, error values, SUBTOTAL and AGGREGATE functions|
|5||Ignore hidden rows|
|6||Ignore error values|
|7||Ignore hidden rows and error values|
We are leaving the link to the Microsoft Support site for the AGGREGATE function, which will help you know the function much deeper.
Let’s write the formula in Excel.
Here we have chosen 6 as our behavior_options. You may wonder why 6, not others?
Ok, look, in the place of the range field, we have divided the number_range by (criteria_range = criteria). From this division, we find an array of Division_Error or the Match Value.
To clarify write this number_range by (criteria_range = criteria) in Excel
And press the F9 key.
We have an array where the elements are the value that has been matched with the criteria or the Division_Error (DIV/0).
We have used 6 as behavior_options to ignore these error elements. You can do this using 7 as well.
For the time being, go with 6, write the formula and hit ENTER.
It has given the second largest value based on the criteria.
Let’s change the criteria and see whether the formula works correctly for that or not.
Here for the club PSG, we have found their second-highest kit’s price.
Let’s see an example where we want to find the value based on the kit version
For the Away Kit, we have found the second-largest value.
3. Using SUMPRODUCT Function
We can find the second highest value using the SUMPRODUCT function as well. The LARGE function will also be needed with the SUMPRODUCT function.
The SUMPRODUCT function multiplies arrays together and returns the sum of products.
SUMPRODUCT (array1, [array2], ...)
array1: The first array or range to multiply, then add.
array2: The second array or range to multiply, then add. This is an optional field.
To know more about the SUMPRODUCT function, visit the Microsoft Support site.
Our generic formula for finding the second largest value based on criteria will be
Write the formula in Excel.
Here within the LARGE function, we have multiplied two arrays together; one array was (criteria_range=criteria) and another one was (number_range).
Multiplying these two we find another array that worked as the array within the LARGE function. From this array, we have found the second-largest value.
Then the SUMPRODUCT function returns the result.
We have found the second-highest kit price for club Barcelona.
You might feel sad if we don’t use Real Madrid as our criteria. Haha! The next example is only for that.
Keep in mind, SUMPRODUCT function will be suitable when you need to check multiple criteria.
That’s all for today. We have tried listing several ways to find the second largest value with criteria in Excel. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know any other methods which we might have missed here.