XLOOKUP was supposed to be the lookup function that finally fixed everything wrong with VLOOKUP. Mostly, it did. But it still breaks in its own specific ways, and when it does, the error codes it throws (#N/A, #VALUE, #SPILL) don't always point you straight to the actual problem.
Here are the seven real causes behind almost every XLOOKUP failure, in the order worth checking them, with a plain fix for each.
Quick Answer: What's usually wrong?
Most XLOOKUP failures come down to one of two things: hidden extra spaces or a data type mismatch between your lookup value and the lookup array (both cause #N/A), or a lookup array and return array that aren't the same size (which causes #VALUE). Wrap your lookup value and array in TRIM, confirm both sides are the same data type with ISNUMBER, and double-check both ranges cover the exact same rows before looking any further.
On this page
- Error 1: #N/A from Hidden Extra Spaces
- Error 2: #N/A from a Data Type Mismatch
- Error 3: #VALUE from Mismatched Array Sizes
- Error 4: #SPILL from a Blocked Range
- Error 5: #REF from a Closed Workbook
- Error 6: Wrong Result After Copying the Formula
- Error 7: XLOOKUP Doesn't Exist in Your Excel Version
- Quick Diagnostic Flow
- How to Avoid Most of These Going Forward
- Key Takeaways
- Frequently Asked Questions (FAQs)
Error 1: #N/A from Hidden Extra Spaces
This is the single most common XLOOKUP failure, and the hardest to spot by eye. A lookup value like "Sub 2" with two spaces between the word and number won't match "Sub 2" with one space, even though they look identical on screen. This is especially common in data imported from another system, copied from a website, or pasted from an email.
=XLOOKUP(TRIM(A2), TRIM(LookupRange), ReturnRange)Wrapping both the lookup value and the lookup array in TRIM() strips leading, trailing, and repeated internal spaces, which resolves this instantly. If TRIM alone doesn't fix it, try adding CLEAN() as well, since some imported text carries non-printing characters that TRIM doesn't catch.
Error 2: #N/A from a Data Type Mismatch
XLOOKUP needs the lookup value and the values in the lookup array to be the same data type. The most common version of this problem is a number that's technically stored as text, usually from an ID column imported from another system. It looks like a number, sits right-aligned or left-aligned inconsistently, but Excel treats it as text underneath.
To check, click an empty cell and type =ISNUMBER(A2) for both your lookup value and a cell in your lookup array. If one returns TRUE and the other FALSE, that's your mismatch. Fix it using Excel's Text to Columns tool (Data tab, Delimited, then choose the correct data type), or wrap the text version in VALUE() to force a conversion.
Error 3: #VALUE from Mismatched Array Sizes
A #VALUE error in XLOOKUP almost always means your lookup array and return array cover a different number of rows or columns. This happens constantly when someone uses Ctrl+Shift+Down to select a column quickly and the selection stops one row short because of a blank cell partway down.
One row missing from either range is enough to trigger the error. Reselect both ranges, or use a Table so this can't happen.
Reselect both ranges carefully, confirming the last row number matches exactly. The more permanent fix is converting your source data into a formal Excel Table (Ctrl+T), since Table references automatically expand and stay synchronized as rows are added or removed.
Error 4: #SPILL from a Blocked Range
If your XLOOKUP is set up to return more than one value at once, for example returning several columns instead of one, it needs a clear block of empty cells to "spill" the results into. If anything, even an empty-looking cell with a leftover space character, sits in that block, you'll get a #SPILL error instead of your result.
Clear the cells in the spill range, or move the formula to a location with enough genuinely empty space around it. Selecting the range and pressing Delete, not just clearing visible content, usually resolves this.
Error 5: #REF from a Closed Workbook
XLOOKUP can reference another workbook, but only while that workbook is open. If you close the source file, formulas referencing it will throw a #REF error the next time they try to recalculate. Reopen the source workbook and the formula typically resolves itself immediately.
Error 6: Wrong Result After Copying the Formula
If your formula returns a plausible-looking answer, but the wrong one, and it worked correctly before you copied it down or across, the lookup or return range likely shifted along with the formula because it used relative references instead of absolute ones.
=XLOOKUP(A2, $D$2:$D$500, $E$2:$E$500)Locking both ranges with dollar signs, as shown above, keeps them fixed no matter where the formula is copied. This is the exact problem Tables solve automatically, since structured references in a Table never shift.
Error 7: XLOOKUP Doesn't Exist in Your Excel Version
XLOOKUP was introduced with Microsoft 365 and Excel 2021. If you're on Excel 2019, 2016, or older, typing XLOOKUP just returns a #NAME error, because the function genuinely doesn't exist in that version. There's no setting to turn on, and no add-in that adds it back.
=INDEX(ReturnRange, MATCH(LookupValue, LookupArray, 0))INDEX combined with MATCH, shown above, is the closest equivalent and works in every version of Excel back to the earliest ones still in common use.
Quick Diagnostic Flow
- Getting #N/A? Check for extra spaces with TRIM, then check data types with ISNUMBER.
- Getting #VALUE? Recheck that your lookup array and return array cover the exact same range.
- Getting #SPILL? Clear the cells around the formula, or move it to open space.
- Getting #REF? Confirm the source workbook is open.
- Getting a wrong but plausible answer? Check whether your ranges used relative references and shifted when copied.
- Getting #NAME? Confirm you're on Microsoft 365 or Excel 2021 or later.
How to Avoid Most of These Going Forward
Beyond that, get in the habit of wrapping lookup values in TRIM() by default whenever your source data comes from outside Excel, and add IFNA() around your XLOOKUP formulas in shared workbooks so a genuine "not found" result shows a clear message instead of a bare error code that confuses whoever opens the file next.
If your lookups are working correctly but the rest of your formulas aren't recalculating the way you expect, that's usually a separate issue. Our guide on why Excel formulas stop updating covers the calculation-mode and formatting problems that cause that specifically.
Key Takeaways
- Extra spaces and data type mismatches cause the large majority of XLOOKUP #N/A errors. TRIM and ISNUMBER catch both.
- #VALUE almost always means your lookup array and return array are different sizes. Converting your data to a Table prevents this permanently.
- #SPILL means something is blocking the cells XLOOKUP needs for a multi-value result. Clear the range or relocate the formula.
- Wrong-but-plausible results after copying a formula usually mean a relative reference shifted. Lock ranges with dollar signs or use a Table.
- XLOOKUP simply doesn't exist before Microsoft 365 and Excel 2021. INDEX plus MATCH is the reliable fallback for older versions.
Frequently Asked Questions (FAQs)
Why does my XLOOKUP show N/A when the value clearly exists?
The two most common reasons are hidden extra spaces around the text, and a data type mismatch, usually a number stored as text. Wrap both the lookup value and lookup array in TRIM to fix spacing, and use a quick ISNUMBER check to confirm both sides are actually the same data type.
Why does XLOOKUP give me a VALUE error?
A VALUE error in XLOOKUP almost always means the lookup array and return array are not the same size, often because one range accidentally excludes the last row or column. Reselect both ranges carefully, or better, convert your data into an Excel Table so the ranges stay synchronized automatically.
What causes a SPILL error in XLOOKUP?
A SPILL error means XLOOKUP is trying to return multiple values into a range of cells, but something else is already sitting in one or more of those cells. Clear the cells below and to the right of the formula, or move the formula somewhere with enough open space.
Can I use XLOOKUP in older versions of Excel?
No. XLOOKUP only exists in Microsoft 365 and Excel 2021 and later. If you are on Excel 2019, 2016, or older, the function is not available at all, and INDEX combined with MATCH is the closest equivalent.
Why does XLOOKUP return the wrong row after I copy the formula?
This usually happens when the lookup array or return array uses relative references and shifts as the formula is copied down or across. Lock both ranges with absolute references using dollar signs, or convert the source data into a Table, which keeps structured references fixed automatically.
Related Articles
External References
- Microsoft Support. "XLOOKUP function." support.microsoft.com
- Excel Campus. "How to Fix and Prevent the VALUE Error with XLOOKUP." excelcampus.com
- Ablebits. "Excel XLOOKUP Function with Formula Examples." ablebits.com
0 Comments