XLOOKUP date of max value

This formula is based on the XLOOKUP function. Working from the inside out, we use the MAX function to calculate a lookup value:

MAX(values)

MAX is nested inside XLOOKUP, and returns a value directly as the first argument:

=XLOOKUP(MAX(values),values,dates)

• The lookup_value is delivered by MAX
• The lookup_array is the named range values (C4:C15)
• The return_array is the named range dates (B4:B15)
• The match_mode is not provided and defaults to 0 (exact match)
• The search_mode is not provided and defaults to 1 (first to last)

Without named ranges

The example uses named ranges for convenience and readability. Without named ranges, the same formula is:

=XLOOKUP(MAX(C4:C15),C4:C15,B4:B15)

Min value

To return the date of the minimum value, the formula in F5 just substitutes the MIN function for the MAX function:

=XLOOKUP(MIN(values),values,dates)

With INDEX and MATCH

The equivalent INDEX and MATCH formula to return the date of max value is:

=INDEX(dates,MATCH(MAX(values),values,0))

Note: although the example uses a vertical range of data, both formulas above will work just as well with a horizontal range.