In the example shown, the formula in F5 is: Note: this is an array formula and must be entered with control + shift + enter. 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: {FALSE;98;FALSE;60;FALSE;95;FALSE} 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
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.
