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.

## 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.

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.

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

`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.

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

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

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.

## 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.