Quick answer
XLOOKUP not working: How to Fix It usually happens because the formula, source data, formatting, or calculation settings do not match what the workbook needs. Start by testing the issue on one simple row before changing the whole file.
What it usually means
The workbook is giving you a warning sign. The fastest fix is to separate the problem into four checks: formula syntax, referenced cells, data type, and workbook settings.
Likely causes
- The lookup value does not exist in the lookup table.
- The lookup column contains extra spaces or numbers stored as text.
- The formula is using approximate match when it should use exact match.
Step-by-step fix
- Check the lookup value manually in the source table.
- Clean both key columns with TRIM or convert numbers stored as text.
- Use exact match in lookup formulas and confirm the return column is correct.
- Test the formula on a known matching value before copying it down.
Useful check formula
=XLOOKUP(A2,Table1[ID],Table1[Result],"Not found")How to prevent it next time
- Use Excel Tables so ranges expand consistently.
- Keep helper columns visible while testing a new formula.
- Document important assumptions directly in the workbook.
- Use IFERROR only after you understand the real cause of the error.