Lookup & Reference
Lookup the date or name of a MAX or MIN value: first and last matching rows
Return the label associated with a maximum or minimum, and choose the first or last matching row when values tie.
Return a label from either side of the value column
The sample contains numeric revenue values in thousands and month labels ordered from January to May. XLOOKUP searches the value column and returns a label from the column to its left.
Default search returns the first matching row; search_mode -1 returns the last matching row. These are earliest and latest dates only when the source is sorted chronologically ascending. Reverse search does not calculate the maximum date. The month labels here are text, not Excel date serials.
Use numeric values throughout the lookup range. If it has no numbers, MAX and MIN can produce zero rather than a meaningful record; validate the range before running the lookup.
| Month (A) | Revenue $k (B) | Peak / Trough Lookup Result |
|---|---|---|
| 2026-01 | $120k | — |
| 2026-02 | $185k (Tied Peak) | First MAX date (default search_mode = 1) |
| 2026-03 | $95k (Trough) | MIN date = "2026-03" |
| 2026-04 / 2026-05 | $150k / $185k (Tied Peak) | Latest MAX date = "2026-05" (search_mode = -1!) |
First matching peak row
Scan top-to-bottom for the first row matching the maximum.
=XLOOKUP(MAX(B2:B6), B2:B6, A2:A6)Last matching peak row
Scan bottom-to-top for the last matching row, regardless of the chronological order of its date label.
=XLOOKUP(MAX(B2:B6), B2:B6, A2:A6, "", 0, -1)Try the example
Use the original values or change them to test your understanding.