To return the largest values in a set of data with criteria, you can use the a formula based on the LARGE and IF functions.

In the example shown, the formula in F5 is:


Note: this is an array formula and must be entered with control + shift + enter.

Generic formula



The LARGE function can be used to retrieve "nth" largest value in numeric data like so:


In this example, we need include only values associated with group B. To do this, we use the IF function to filter:


Since we are running a logical test on an range of cells, we get an array of results:


Note that only values in group B make it into the array. Group A values become FALSE since they fail the logical test. This array is returned inside the LARGE function with 2 hardcoded as as "nth" (the argument "k" in LARGE):


LARGE then returns 95, the second largest value in group B as the final result.

Multiple criteria

To take into account multiple criteria, you can extend the formula with boolean logic in a form like this:


Where criteria1 and criteria2 and represent an expression to test values in a criteria range, as shown in the original example above.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.