Explanation

The LARGE function is an easy way to get the nth largest value in a range:

 =LARGE(range,1) // 1st largest
 =LARGE(range,2) // 2nd largest
 =LARGE(range,3) // 3rd largest

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:

LARGE(IF(range="A",range2),1)

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:

LARGE(IF(group="A",score),F5)

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:

LARGE({79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE},1)

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:

=INDEX(name,MATCH(93,IF(group="A",score),0))

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:

MATCH(93,{79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE},0)

Since 93 appears in the 3rd position, MATCH returns 3 directly to the INDEX function :

=INDEX(name,3) // Hannah

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:

=XLOOKUP(LARGE(IF(group="A",score),F5),IF(group="A",score),name)

As above, LARGE is configured to work with an array filtered by IF, and returns a result of 93 to XLOOKUP as the lookup value:

=XLOOKUP(93,IF(group="A",score),name) // Hannah

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

  1. To get the name of nth value with criteria , (i.e. limit results to group A or B) you will need to extend the formula to use additional logic .
  2. In Excel 365 , the FILTER function is a better way to list top or bottom results dynamically . This approach will automatically handle ties.

Explanation

In this example, the goal is to extract the top 3 quiz scores for each name from the 5 scores that appear in columns C, D, E, F, and G. In other words, for each name listed, we want the best score, the 2nd best score, and the 3rd best score. This problem can be solved with the LARGE function.

Note: I don’t know why the second argument for LARGE is called “k” . In this article I pretty much ignore that fact and use “n” instead, since “nth” is easier to understand than “kth”.

LARGE function

The LARGE function can be used to return the nth largest value in a set of data. The generic syntax for LARGE looks like this:

=LARGE(range,n)

where n is a number like 1, 2, 3, etc. For example, you can retrieve the first, second, and third largest values like this:

=LARGE(range,1) // first largest
=LARGE(range,2) // second largest
=LARGE(range,3) // third largest

The LARGE function is fully automatic — you just need to supply a range and a number that indicates rank. The official names for these arguments are " array " and " k “. To illustrate, below we use LARGE to get the top 3 scores in column C. The formula in F5, copied down, is:

=LARGE(data,E5)

Data is the named range C5:C16, provided as the array argument, and the value for k (n) comes from column E. As the formula is copied down, it returns the top 3 scores:

Nth largest value - basic example - 1

Here, data is the named range C5:C16, and the value for n comes from column E.

Mixed references

In the worksheet shown at top, we can use LARGE to get the top 3 scores for Hannah like this:

 =LARGE(C5:G5,1) // best score
 =LARGE(C5:G5,2) // 2nd best score
 =LARGE(C5:G5,3) // 3rd best score

The main challenge in this example is to create the syntax needed to copy the formula across the range I5:K16. In the example shown, this is accomplished with the formula in cell I5:

=LARGE($C5:$G5,I$4)

This is a clever use of mixed references that takes advantage of the fact that the numbers 1, 2, and 3 are already in the range I5:K5, so that they can be plugged into the formula directly as n:

  • The value given for array is the mixed reference $C5:$G5. Notice that the columns are locked, but rows are not. This allows the rows to update as the formula is copied down , but prevents columns from changing as the formula is copied across .
  • The value given for k (n) is another mixed reference, I$4. Here, the row is locked so that it will not change as the formula is copied down . However, the column is not locked, allowing it to change as the formula is copied across .