Excel XLOOKUP Max Date: How to Find Latest Entry

Formulas & functions · Updated November 22, 2026

To find the max date with XLOOKUP in Excel, use the formula =XLOOKUP(MAX(date_range), date_range, return_range). This returns the value from the return range that corresponds to the latest date in the date range. If you need the actual max date itself, simply use =MAX(date_range).

Why Use XLOOKUP for Max Dates?

XLOOKUP, available in Excel 2021 and Microsoft 365, replaces older functions like VLOOKUP and HLOOKUP. It can search in any direction, return multiple values, and handle errors gracefully. When you need to pull information associated with the most recent date—like the latest price, status, or sales figure—XLOOKUP combined with MAX is the cleanest approach. Unlike VLOOKUP, XLOOKUP does not require the lookup column to be the leftmost column, and it defaults to an exact match, which is what you want when working with dates.

Basic XLOOKUP Max Date Formula

Assume your dates are in A2:A20 and the values you want to retrieve are in B2:B20. To get the value for the latest date, use:

=XLOOKUP(MAX(A2:A20), A2:A20, B2:B20)

This formula first calculates MAX(A2:A20) to find the latest date, then XLOOKUP finds that exact date in the same range and returns the corresponding value from B2:B20. For example, if the latest date is 2025-03-15 and B5 contains 450, the formula returns 450.

If your dates are not sorted, XLOOKUP still works because it searches the entire array. If there are duplicate dates, XLOOKUP returns the first match—the first row with that max date. To get the last match instead, add the optional search mode argument: =XLOOKUP(MAX(A2:A20), A2:A20, B2:B20, , , -1).

Finding the Max Date with Criteria (Conditional)

Often you need the latest date for a specific category, like the most recent order for a customer. Use the FILTER function to narrow the range first. With customers in A2:A20, dates in B2:B20, and sales in C2:C20, to get the latest sale for “Acme Corp”:

=XLOOKUP(MAX(FILTER(B2:B20, A2:A20="Acme Corp")), FILTER(B2:B20, A2:A20="Acme Corp"), FILTER(C2:C20, A2:A20="Acme Corp"))

This filters the dates to only those for Acme Corp, finds the max, then XLOOKUP retrieves the corresponding sales amount. If no matches exist, FILTER returns a #CALC! error; wrap it with IFERROR to show a friendly message: =IFERROR(..., "No data").

Alternatives to XLOOKUP for Max Dates

XLOOKUP is not the only option. Depending on your Excel version and preference, consider:

  • INDEX/MATCH: Works in all Excel versions. Formula: =INDEX(B2:B20, MATCH(MAX(A2:A20), A2:A20, 0)). This is the classic predecessor to XLOOKUP.
  • LOOKUP: Legacy function, but requires sorted data for reliable results, so avoid it for unsorted dates.
  • MAXIFS with INDEX/MATCH: For criteria, use =INDEX(C2:C20, MATCH(MAXIFS(B2:B20, A2:A20, "Acme Corp"), B2:B20, 0))—but this can return the wrong row if the max date appears for another customer. Better to use FILTER as shown above.

For simply returning the latest date itself, =MAX(range) is the most direct and efficient formula.

Common Errors and How to Fix Them

  • #N/A error: The MAX date is not found in the lookup array. This can happen if the date in the lookup array is stored as text. Convert text dates to real dates using DATEVALUE or the Text to Columns wizard.
  • #VALUE! error: The lookup array and return array have different sizes. Ensure both ranges have the same number of rows.
  • Wrong result due to unsorted data: XLOOKUP handles unsorted data fine, but if you used LOOKUP, it may fail. Stick with XLOOKUP or INDEX/MATCH.
  • Dates as text: If your dates are left-aligned in cells, they are likely text. Select the column, go to Data > Text to Columns > Delimited > Finish to convert them to real dates.

Using XLOOKUP with Max Date in Excel for Mac and Web

The formulas above work identically in Excel for Microsoft 365 on Windows, Mac, and Excel for the web. The only difference is keyboard shortcuts: on Mac, use Cmd+Enter instead of Ctrl+Enter to enter a formula in multiple cells. XLOOKUP is available in Excel 2021 and later, and in Microsoft 365 on all platforms. If you are on an older version, use INDEX/MATCH instead.

Performance and Large Datasets

XLOOKUP is fast, but if you have hundreds of thousands of rows, consider using a helper column with MAXIFS or a PivotTable to avoid recalculating the entire array. For a single max date lookup, XLOOKUP is efficient. If you need to pull multiple columns for the max date, use XLOOKUP with an array return: =XLOOKUP(MAX(A2:A20), A2:A20, B2:D20) returns three values spill down. This works in Excel 365 and 2021.

Frequently Asked Questions

Can XLOOKUP return the max date itself?

No, XLOOKUP returns a value from a return array. To get the max date, use =MAX(A2:A20) directly. If you need to display the date in a formatted way, use TEXT: =TEXT(MAX(A2:A20),"mm/dd/yyyy").

How do I find the max date for each category?

Use a combination of MAXIFS and XLOOKUP, or better, use a PivotTable. For a formula approach, with categories in A2:A20 and dates in B2:B20, you can list unique categories in D2:D5 and use =MAXIFS($B$2:$B$20,$A$2:$A$20,D2) to get the max date per category.

Why does XLOOKUP return the wrong date when there are duplicates?

By default, XLOOKUP returns the first match. If you want the last match, use the search mode argument: =XLOOKUP(MAX(A2:A20), A2:A20, B2:B20, , , -1). The -1 tells Excel to search from last to first.

Does XLOOKUP work with text dates?

No, XLOOKUP requires the lookup value and lookup array to be the same data type. If your dates are text, convert them using DATEVALUE or by using the Text to Columns wizard. Then recalculate.

Can I use XLOOKUP to find the max date across multiple columns?

Yes, use MAX on a 2D range: =XLOOKUP(MAX(A2:C20), A2:C20, D2:D20)—but this only works if the return array is the same shape. If you have dates in a matrix, consider using MAX on the entire range and then INDEX/MATCH with a flattened array, which is more complex. A simpler method is to use a helper column that consolidates dates.

What if I have blank cells in the date range?

MAX ignores blank cells, so that’s fine. But if there are text strings, MAX will ignore them too. If you have errors, wrap with IFERROR: =MAX(IFERROR(A2:A20,0)) as an array formula (Ctrl+Shift+Enter on older Excel, but in 365 it works normally).

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.