Formula guide · Excel · Updated May 13, 2026
XLOOKUP Not Working
Fix XLOOKUP problems caused by missing matches, hidden spaces, mismatched ranges, or unsupported Excel versions.
Quick Answer
When XLOOKUP is not working, first check whether the lookup value truly exists, then confirm lookup_array and return_array are the same size.
Copyable Formula
=XLOOKUP(TRIM(E2),TRIM(A2:A100),D2:D100,"Not found")Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, "Not found")Excel
Worked Example
| Symptom | Likely fix |
|---|---|
| #N/A | Check missing values and hidden spaces |
| Wrong result | Check the selected return range |
| Formula name error | Check Excel version support |
Result: Most XLOOKUP issues are data quality or range selection issues, not a broken function.
Steps
- Search for the lookup value manually to confirm it exists.
- Use TRIM or CLEAN when values came from pasted or imported data.
- Make sure lookup_array and return_array have the same height or width.
- Add a clear not-found message only after the source issue is understood.
Common Mistakes
- Using IFERROR before checking why the match failed.
- Selecting a return range that starts on a different row than the lookup range.
- Using XLOOKUP in an older Excel version that does not support it.
Excel vs Google Sheets Notes
This page is focused on Excel. If you need Google Sheets compatibility, check whether your Sheets account supports the same function or use one of the related alternatives.
Editorial check: This guide was last updated May 13, 2026. Formula behavior can vary by Excel version, Google Sheets rollout, and spreadsheet locale.