Excel Find Second Largest Value with Criteria

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.

Excel Sheet - Excel Find Second Largest Value with Criteria

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.

Practice Workbook

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.

Criteria scenario - Excel Find Second Largest Value with Criteria

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.

LARGE(number_range, k)

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

LARGE(IF(criteria_range=criteria,number_range),2))

Write the formula in Excel.

LARGE - Excel Find Second Largest Value with Criteria

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.

LARGE function result - Excel Find Second Largest Value with Criteria

We have found the second-largest price for the Chelsea kit.

See another example, let’s change the club from Chelsea.

Example - Excel Find Second Largest Value with Criteria

We have changed the club name (criteria), and it returned the second-highest value for that.

Let’s change the criteria.

Version Criteria - Excel Find Second Largest Value with 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

AGGREGATE(function_number,behavior_options, range)

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

Function Function_number
AVERAGE 1
COUNT 2
COUNTA 3
MAX 4
MIN 5
PRODUCT 6
SUM 9
LARGE 14
SMALL 15

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

AGGREGATE(14,behavior_options,range,k)

Here k is an integer that specifies the position from the largest value.

There are 8 possible values for behavior_options. They are

Value Behavior
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
4 Ignore nothing
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.

AGGREGATE- Excel Find Second Largest Value with Criteria

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

range array -Excel Find Second Largest Value with Criteria

And press the F9 key.

range array output-Excel Find Second Largest Value with Criteria

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.

AGGREGATE result - Excel Find Second Largest Value with Criteria

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.

Example of AGGREGATE- Excel Find Second Largest Value with Criteria

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

Version criteria example - Excel Find Second Largest Value with Criteria

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

SUMPRODUCT(LARGE((criteria_range1=criteria1)*(criteria_range2=criteria1)*(number_range),2))

Write the formula in Excel.

SUMPRODUCT-Excel Find Second Largest Value with Criteria

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.

SUMPRODUCT result-Excel Find Second Largest Value with Criteria

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.

Example of SUMPRODUCT-Excel Find Second Largest Value with Criteria -

Keep in mind, SUMPRODUCT function will be suitable when you need to check multiple criteria.

Conclusion

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo