XLOOKUP Returns #N/A / Not Found — Complete Fix Guide
Excel's modern lookup function still fails the same old ways — here's every cause and the cleanest fix for each.
Table of Contents
- 1. Why XLOOKUP Still Shows #N/A
- 2. Interactive Demo: Built-In Fallback Behavior
- 3. The Five Root Causes
- 4. Using if_not_found Correctly
- 5. Lookup Array and Return Array Size Mismatch
- 6. Wildcard Match Mode Mistakes
- 7. Approximate Match Mode Confusion
- 8. XLOOKUP vs VLOOKUP Error Handling
- 9. Google Sheets XLOOKUP Support
- 10. Frequently Asked Questions
1. Why XLOOKUP Still Shows #N/A
XLOOKUP was designed specifically to fix many of VLOOKUP's long-standing frustrations — it can look left, doesn't break when columns are inserted, and defaults to exact match. But it still returns #N/A under the same fundamental condition as every lookup function before it: when no match exists in the lookup array for the given search value. The good news is XLOOKUP gives you a built-in, elegant way to handle that case directly in the formula itself.
2. Interactive Demo: Built-In Fallback Behavior
See exactly how the if_not_found argument changes XLOOKUP's behavior compared to leaving it out.
3. The Five Root Causes
- The search value genuinely doesn't exist anywhere in the lookup array
- Hidden whitespace or non-breaking spaces causing a technically different value
- Text vs number data type mismatch between the search value and lookup array
- Lookup array and return array are different sizes (a required condition for XLOOKUP to function)
- Wildcard match mode enabled unintentionally, or disabled when it was needed
4. Using if_not_found Correctly
This is XLOOKUP's single biggest improvement over VLOOKUP for error handling — a dedicated fourth argument built directly into the function:
=XLOOKUP(A2, LookupArray, ReturnArray, "Not found")This is meaningfully better than wrapping the whole formula in IFERROR(), because if_not_found only catches the specific "genuinely no match" condition — it lets other real problems, like a #REF! from a deleted range, still surface as visible errors you'd want to notice and investigate, rather than silently hiding every possible error behind one generic fallback.
5. Lookup Array and Return Array Size Mismatch
XLOOKUP requires the lookup_array and return_array to have the same dimensions — same number of rows if both are vertical, or same number of columns if both are horizontal. If they don't match, XLOOKUP returns a dedicated #VALUE! error rather than #N/A, which is a useful clue for diagnosing this specific mistake:
=XLOOKUP(A2, B2:B100, C2:C50) → #VALUE! (100 rows vs 50 rows mismatch)
=XLOOKUP(A2, B2:B100, C2:C100) → Correct (matching row counts)6. Wildcard Match Mode Mistakes
XLOOKUP's fifth argument (match_mode) can be set to 2 for wildcard matching, allowing * and ? as pattern characters. If wildcard mode is off but your search text accidentally contains a literal asterisk (common in some product codes), XLOOKUP will search for that exact character rather than treating it as a wildcard, causing an unexpected #N/A:
=XLOOKUP("Product*", LookupArray, ReturnArray, "Not found", 2) — wildcard mode enabled
=XLOOKUP("Product*", LookupArray, ReturnArray, "Not found", 0) — literal match, searches for an actual asterisk character7. Approximate Match Mode Confusion
XLOOKUP's match_mode argument also supports -1 (exact match or next smaller item) and 1 (exact match or next larger item), useful for tiered lookups like commission brackets or shipping rate tables. A common mistake is leaving match_mode at its default of 0 (exact match only) when the data genuinely requires approximate matching against sorted breakpoints, causing #N/A on any value that falls between two listed breakpoints rather than matching one exactly.
8. XLOOKUP vs VLOOKUP Error Handling
| Aspect | VLOOKUP | XLOOKUP |
|---|---|---|
| Built-in fallback | None — requires wrapping in IFERROR | Native if_not_found argument |
| Default match type | Approximate (dangerous default) | Exact (safer default) |
| Column insertion safety | Breaks if columns are inserted between lookup and return columns | References return array directly, unaffected by inserted columns |
| Search direction | Only searches left to right | Can search in any direction, including right to left |
9. Google Sheets XLOOKUP Support
Google Sheets supports XLOOKUP natively with largely identical syntax and behavior, including the if_not_found argument working the same way. The main difference worth noting is that XLOOKUP was added to Sheets somewhat later than to Excel, so if you're collaborating with someone using an older Sheets interface or a very old browser cache, confirm both parties have a modern enough version before assuming a shared formula will behave identically.
10. Frequently Asked Questions
Why does XLOOKUP return #N/A even though the value exists?
This is usually caused by a data type mismatch (text vs number), hidden whitespace or non-breaking spaces in one of the values, or the lookup array and return array not being properly aligned in size and orientation.
How do I stop XLOOKUP from showing #N/A?
Use the built-in fourth argument of XLOOKUP, if_not_found, to supply a custom fallback value directly within the function itself, such as =XLOOKUP(A2,LookupRange,ReturnRange,"Not found"), which is cleaner than wrapping the entire formula in IFERROR.
What is the difference between XLOOKUP's if_not_found and IFERROR?
The if_not_found argument only catches a genuine no-match condition and lets other real errors (like a reference error) still surface visibly. IFERROR wrapped around the whole formula would silently hide every type of error, including ones you might actually want to notice and fix.
Related Guides
- VLOOKUP #N/A on Identical-Looking Values
- INDEX/MATCH Returning #N/A
- Browse the Full 266+ Error Directory
Editorial Disclaimer: This guide is developed to the best of our domain knowledge and tested against Excel 2019+, Microsoft 365, and Google Sheets. Content is updated continuously as spreadsheet calculation engines evolve.