Name of nth largest value

To get the name of the nth largest value in a range, use the XLOOKUP functions with the LARGE function together.

In the example, we will use the LARGE function to calculate the lookup value based on the XLOOKUP function. The LARGE function uses simple logic to find the nth largest value in a range. For example, to extract the largest value from the range C3:C10, use the formula:

=LARGE(sales, 1), where ‘sales’ is a named range.

large-function-get-the-nth-largest-value

XLOOKUP gets the name of the nth largest value

In the example, we want to find the largest value in a range, so the function returns $3117. In other words, we now have the XLOOKUP function’s first argument.

Let’s configure the arguments:

  • lookup_value: =LARGE(sales,1)
  • lookup_array is named range, ‘sales‘ (C3:C10)
  • return_array is a named range, ‘products’ (B3:B10)

In this case, we are not using the optional arguments to find the nth largest value. Instead, XLOOKUP will perform the lookup based on the LARGE(sales,1) expression and check the range C3:C10. If the function finds a match, it will return the name of the corresponding item.

XLOOKUP-first-largest-value

Workaround with multiple matches (duplicated values)

What if we have multiple matches? In the following example, we want to create a formula to find the third-largest value in a range. The nth largest values may be the same in Excel, and the LARGE will return multiple matches.

Take a closer look at the picture below:

multiple-matches

Use the FILTER function to extract all records that meet the criteria.

=FILTER(B3:C10,sales=LARGE(sales,3)

filter-multiple-nth-largest-value-in-Excel

Related Formulas: