Most XLOOKUP errors come from one of four things: no true match exists, the source data is dirty, the lookup and return ranges do not line up, or the workbook is not ready for a spilled result.
That is good news, because it means XLOOKUP is usually not failing randomly. There is normally a concrete cause you can find and fix.
If you need the base formula first, go back to XLOOKUP Function in Excel: Step-by-Step Guide for Beginners. This article starts where that one ends: when the formula looks right but the result does not.
Error 1: #N/A Because No Exact Match Was Found
This is the most common issue.
If your formula is:
=XLOOKUP(A2,$E$2:$E$20,$F$2:$F$20)
and Excel returns #N/A, first ask the simplest question: does the lookup value actually exist in E2:E20?
If not, the formula is behaving correctly.
To make the workbook easier to read, use the if_not_found argument:
=XLOOKUP(A2,$E$2:$E$20,$F$2:$F$20,"Not found")
That is better for dashboards, staff sheets, and lists used by non-technical readers.
Microsoft also has a broader guide to correcting a #N/A error when the problem is not limited to XLOOKUP.
Error 2: The Match Looks Right, but Hidden Spaces Break It
Sometimes the value seems to exist, but Excel still cannot match it. The reason is often extra spaces or imported text problems.
For example, INV-1001 and INV-1001 are not the same to Excel.
Use TRIM to clean obvious spacing issues:
=XLOOKUP(TRIM(A2),$E$2:$E$20,$F$2:$F$20,"Not found")
If the source column is dirty too, you may need to clean both sides of the lookup. This problem is not unique to XLOOKUP, which is why VLOOKUP Not Working with Text Values: 6 Fixes You Need is still useful here.
If your problem is broader than spacing, How to Use XLOOKUP with Wildcards for Partial Matches is the next article to read.
Error 3: Lookup and Return Ranges Do Not Match
The lookup_array and return_array should describe the same rows or the same columns.
Bad example:
=XLOOKUP(A2,$E$2:$E$20,$F$2:$F$19)
Better example:
=XLOOKUP(A2,$E$2:$E$20,$F$2:$F$20)
If the ranges are different sizes, Excel may return an error or a result that is harder to trust.
This is one of the first things to inspect when a formula seems syntactically correct but still behaves strangely.
Error 4: Spill Errors from Multi-Value Returns
If you use XLOOKUP to return multiple columns or rows, Excel may spill the results into nearby cells. That is useful, but it also creates a new failure mode: the spill area might be blocked.
Example:
=XLOOKUP(A2,$B$2:$B$20,$C$2:$E$20)
If one of the target cells already contains data, Excel cannot place the result there.
That is not an XLOOKUP logic problem. It is a worksheet layout problem.
If this is the issue you are seeing, read How to Return Multiple Columns and Rows with XLOOKUP next.
Error 5: Wrong Match Mode or Search Mode
Many readers do fine with the default exact-match behavior and never need to touch match_mode or search_mode.
Problems start when one of these optional arguments is used without the right source data.
For example:
=XLOOKUP(B2,$J$2:$J$7,$K$2:$K$7,,-1)
This asks Excel for an exact match or the next smaller value. That only makes sense when the lookup logic and source ordering fit the problem.
Binary search modes are even more sensitive because they assume sorted data. If the table is not sorted correctly, the result may be invalid even though the formula itself is accepted.
For the exact syntax and argument meanings, use the official XLOOKUP documentation.
A Fast Troubleshooting Order
When XLOOKUP is not working, check in this order:
- Does the value really exist in the lookup range?
- Are there hidden spaces or dirty text values?
- Do the lookup and return ranges line up exactly?
- Is the spill area blocked?
- Did I change
match_modeorsearch_modewithout the right data setup?
That sequence saves time because it starts with the most common and least complicated problems.
The Best Preventive Habits
If you want fewer lookup problems later, build these habits now:
- use exact match as your default,
- add
if_not_foundwhen the workbook will be shared, - keep lookup and return ranges aligned,
- clean imported text before building formulas,
- test one known-good value before copying the formula down a whole column.
If your next question is whether a legacy workbook should keep VLOOKUP or move to XLOOKUP, read XLOOKUP vs VLOOKUP in Excel: Which Should You Use?.
Verdict
Most XLOOKUP errors are not mysterious. They are usually data quality, range alignment, or worksheet layout issues.
Once you learn to check those first, XLOOKUP becomes much easier to trust and much faster to debug.