# XLOOKUP date of max value

To use XLOOKUP to find the date of the max value, you can use the MAX function and XLOOKUP together. In the example shown, the formula in F4 is:

where **values** (C4:C15) and **dates** (B4:B15) are named ranges.

*beta function*, available only through the Office Insiders program.

### How this formula works

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:

- The
*lookup_value*is delivered by MAX - The
*lookup_array*is the named range**values**(C4:C15) - The
*return_array*is 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 names ranges, the same formula is:

### Min value

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

### With INDEX and MATCH

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

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

## Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.