where name (B5:B16), group (C5:C16), and score (D5:D16) are named ranges. The formula returns the name associated with the 1st, 2nd, and 3rd highest values in Group A. Note: This is an array formula that must be entered with control + shift + enter, except in Excel 365. In this example, we can use the LARGE function to get a highest score, then use the score like a “key” to retrieve the associated name with INDEX and MATCH. Notice we are picking up the values for n from the range F5:F7, in order to get the 1st, 2nd, and 3rd highest scores. The twist however in this case is that we need to distinguish between scores in group A and group B. In other words, we need to apply criteria. We do this with the IF function, which is used to “filter” values before they are evaluated with LARGE. As a generic example, to get the largest value (i.e. 1st value) in range2 where range 1 = “A”, you can use a formula like this: Note: using IF this way makes this an array formula. Working from the inside out, the first step is to get the “1st” largest value in the data associated with Group A with the LARGE function: In this case, the value in F5 is 1, so we are asking for the top score in Group A. When the IF function is evaluated, it tests each value in the named range group. The named range score is provided for value_if_true. This generates a new array, which is returned directly to the LARGE function: Notice the only scores that survive the filter are from Group A. LARGE then returns the highest remaining score, 93, directly to the MATCH function as a lookup value. We can now simplify the formula to: Now we can see that the MATCH function is configured to use the same filtered array we saw above. The IF function again filters out unwanted values, and the MATCH portion of the formula resolves to: Since 93 appears in the 3rd position, MATCH returns 3 directly to the INDEX function: Finally, the INDEX function returns the name in the 3rd row, “Hannah”.
With XLOOKUP
The XLOOKUP function can also be used to solve this problem, using the same approach explained above: As above, LARGE is is configured to work with an array filtered by IF, and returns a result of 93 to XLOOKUP as the lookup value: The lookup array is also created by using IF as a filter on scores from Group A. With the return array provided as name (B5:B16). XLOOKUP returns “Hannah” as the final result.
Notes
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.
