XLOOKUP Errors in Google Sheets: #N/A, #REF!, #VALUE!

XLOOKUP removes most of VLOOKUP’s failure modes, but it has a short error vocabulary of its own, and one of its errors has two very different causes. Every diagnosis below was reproduced in a live sheet before it went in this guide.

In This Guide

• #N/A: Not Found
• #N/A: Mismatched Range Sizes
• #REF!: The Blocked Spill
• #VALUE!: The Missing ARRAYFORMULA
• Wrong Value, No Error
• Related Google Sheets Tutorials

#N/A: Not Found

The honest error: the search key is not in the lookup range.

When the value should be there but XLOOKUP disagrees, the mismatch is almost always invisible formatting:

• Stray spaces. "SKU-103 " with a trailing space does not equal "SKU-103". Clean with TRIM, or check with =LEN(cell) against the length you expect.
• Numbers stored as text. A text "42" does not match a numeric 42. Text numbers align left by default; convert with VALUE, or re-enter the column with a number format.
• Different characters that look alike. Data pasted from other tools can carry non-breaking spaces or curly quotes. Re-typing one failing key by hand tells you quickly whether the visible text is the real text.

When the key genuinely may be absent, that is not an error to fix but a case to handle: the missing_value argument exists for exactly this.

#N/A: Mismatched Range Sizes

The trap: XLOOKUP also fails with #N/A when the lookup range and result range are different sizes, even for a key that is right there in the range.

Five lookup cells, four result cells: the formula returns #N/A despite “Red” sitting in A2. We verified this in a live sheet, and it is the most misleading error on this page because it impersonates a not-found result. If a lookup that must succeed returns #N/A (or your missing_value fallback), compare the two ranges’ sizes before anything else.

#REF!: The Blocked Spill

A multi-column result range spills its extra values into neighboring cells. If any of those cells holds data, nothing spills and the formula returns #REF!:

With anything typed two cells to the right, this shows #REF!. Clear the spill zone (the result range’s width minus one, to the right of the formula; for horizontal lookups, below it) and the values appear.

#REF! also appears for the ordinary reason any formula gets it: a range that no longer exists because its sheet or columns were deleted. If your formula references another sheet, check the sheet name still matches.

#VALUE!: The Missing ARRAYFORMULA

The Excel-import special. Excel evaluates array expressions inside XLOOKUP automatically, so its multiple-criteria pattern looks like this:

In Google Sheets, that exact formula returns #VALUE!. Sheets does not evaluate the elementwise comparison arrays unless the formula is wrapped in ARRAYFORMULA:

We verified both behaviors live: bare fails with #VALUE!, wrapped returns the right row. The concatenation variant of the same trick fails as #N/A instead, which is stranger to debug; the full story is in XLOOKUP with multiple criteria.

A #VALUE! can also come from a search key that is itself an error (check the cell your formula points at), or from handing a 2-D block to the lookup_range, which must be a single row or column.

Wrong Value, No Error

The worst failure returns confidently and incorrectly. XLOOKUP has exactly two ways to do this, and both are opt-in:

• An approximate match you did not mean. Match_mode -1 or 1 returns neighbors of missing keys by design. If a formula you inherited returns near-misses, look for a -1 or 1 in the fifth argument.
• Binary search on unsorted data. Search_mode 2 and -2 assume sorted ranges and quietly break on unsorted ones. Remove the sixth argument unless the range is large and reliably sorted.

On default settings, exact match with a top-down scan, XLOOKUP does not return wrong rows silently. That is the single biggest reliability difference from VLOOKUP, whose approximate default made silent wrongness the out-of-the-box behavior.

Related Google Sheets Tutorials

• XLOOKUP: The Complete Guide: Syntax and all six arguments
• XLOOKUP If Not Found: Turning #N/A into useful output
• XLOOKUP with Multiple Criteria: The ARRAYFORMULA story in full
• XLOOKUP Return Multiple Columns: Spills and their space needs
• Fix VLOOKUP Errors: The same debugging mindset for the older function