XLOOKUP doesn't work properly with date range

Hi,

Additionally, ensure that the calculation mode is set to automatic.

Hello Angko Hayes,

Thank you for reaching out in Microsoft Q&A forum.

I understand you're trying to use XLOOKUP with dates that come from formulas and it doesn’t return the correct result, even after copying the values.

XLOOKUP can only match correctly when the lookup values are true Excel date‑serial numbers. When a date is created by another formula, sometimes the result is text instead of a real date, and XLOOKUP cannot match text against date‑serial numbers. That is why XLOOKUP only works again after you click each cell and press Enter.

To fix this without re‑entering every date manually, convert the formula results into actual dates:

1. Select the formula‑generated date column.
2. Copy > Paste Special > Values.
3. With the same range selected, use Data > Text to Columns > Finish (this forces Excel to convert text‑dates into real dates).
4. Run XLOOKUP again.

If you prefer a formula‑only solution, wrap your date formula in DATEVALUE(), for example: =DATEVALUE(your_formula_here)

Or, if your lookup range may contain mixed types, use: =XLOOKUP(--lookup_cell, --lookup_range, return_range)

(The double minus converts text to real numbers/dates so XLOOKUP can match consistently.)

Hope this helps

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".  

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread

It seems that you are experiencing issues with the XLOOKUP function when working with date ranges in Excel. Here are a few suggestions that might help you resolve the problem:

1. Ensure Date Formats: Make sure that the dates in both the lookup array and the return array are formatted as dates. Sometimes, dates can be stored as text, which can lead to mismatches in lookups.
2. Use Helper Columns: If XLOOKUP is not returning the expected results, consider creating a helper column that converts the date values to a standard format using the DATEVALUE function. This can help ensure that all dates are recognized correctly.
3. Alternative Functions: If XLOOKUP continues to be problematic, you could try using other functions like INDEX and MATCH to achieve similar results. For example:

      =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
   

   This combination can sometimes handle date lookups more effectively, especially if you are dealing with ranges.
4. Recalculate Workbook: If you find that you need to click into each cell to refresh the values, you can force Excel to recalculate all open workbooks by pressing CTRL + ALT + F9. This can help ensure that all formulas are up to date.
5. Check for Volatile Functions: If your formulas depend on volatile functions, consider making your custom functions volatile by adding Application.Volatile in your VBA code. This will ensure that the function recalculates whenever changes are made in the workbook.

If these suggestions do not resolve your issue, please provide more details about the specific formulas and data you are working with for further assistance.

References:

• Excel performance: Tips for optimizing performance obstructions
• Custom function may not calculate expected value in Excel