XLOOKUP is simpler than VLOOKUP until it returns an error. The function has fewer arguments, but it is strict about range sizes, data types and the space available for a spilled result. The error code tells you which rule you broke.
Quick answer: #NAME? means your Excel version does not have XLOOKUP. #N/A means no exact match was found. #VALUE! usually means the lookup and return ranges are different sizes. #SPILL! means something is blocking the result. Fix the error code first, then check data types and hidden spaces.
Error Code → Cause → Fix
| Error | Most likely cause | Fix |
|---|---|---|
#NAME? | XLOOKUP is not available in this Excel version | Use INDEX/MATCH or upgrade to Microsoft 365 / Excel 2021+ |
#N/A | No exact match, or values differ by type or spaces | Clean both sides, check ISNUMBER, use if_not_found |
#VALUE! | Lookup array and return array are different sizes | Use equal-height ranges or whole structured columns |
#SPILL! | The return array cannot expand because cells below are occupied | Clear the spill range or return a single value |
#REF! | A referenced range or sheet was deleted | Repair the reference; avoid whole-column references to closed books |
| Wrong value, no error | Approximate match or duplicate keys | Set match_mode to 0 and confirm the key is unique |
1. #NAME? Means the Function Does Not Exist
XLOOKUP requires Microsoft 365, Excel 2021, Excel 2024 or Excel for the web. Excel 2019 and 2016 do not have it, and no amount of syntax fixing will help. Check File → Account → About Excel, or type =XLOOKUP( and see whether Excel suggests the function.
If you are on an older version, use =INDEX(return_range,MATCH(lookup_value,lookup_range,0)). It is the closest equivalent and works in every modern version. The XLOOKUP vs VLOOKUP guide shows both syntaxes side by side.
2. #N/A: No Exact Match Was Found
XLOOKUP's default match mode is exact, so #N/A is honest: the value is not in the lookup array. The usual reasons are:
- Trailing spaces.
"North "is not"North". Wrap both sides inTRIM, or clean the source column. - Text vs number. Text
"1001"does not match numeric1001. Test withISNUMBERand convert one side. - Hidden characters. Non-breaking spaces and line breaks survive TRIM. Use
CLEANandSUBSTITUTE(...,CHAR(160)," "). - Different ranges. The lookup array may start one row lower than the return array, so the matched position returns the wrong row.
- Duplicate keys. XLOOKUP returns the first match. If the key is not unique, confirm that the first match is the one you want.
Add a clear fallback so the sheet stays readable: =XLOOKUP(A2,Table[ID],Table[Name],"Not found",0). A visible label is easier to audit than a wall of #N/A.
3. #VALUE!: The Ranges Are Different Sizes
The lookup array and return array must have the same number of rows. =XLOOKUP(A2,B2:B100,C2:C90) returns #VALUE! because the arrays do not line up. This often happens when one range was extended and the other was not.
The safest fix is to use structured references from an Excel Table: =XLOOKUP([@ID],tblProducts[ID],tblProducts[Name],"Not found"). A Table keeps every column the same length when rows are added, which removes this class of error.
4. #SPILL!: Something Is Blocking the Result
If the return array contains multiple cells, XLOOKUP spills the result down the sheet. #SPILL! means one or more cells in that area already contain data, a merged cell, or a table boundary.
- Select the cell with the error; Excel outlines the blocked spill range.
- Delete or move the content inside the outline.
- If you only need one value, return a single column instead of a multi-column array.
- Do not place a spill formula inside an Excel Table; use a normal range or a single-cell formula.
For a multi-column lookup, =XLOOKUP(A2,IDs,Names) spills one column. =XLOOKUP(A2,IDs,Data) where Data is three columns spills three columns and needs three free cells.
5. The Formula Returns the Wrong Match
Check the fifth argument, match_mode. The default is 0 (exact match). -1 returns the next smaller item and 1 returns the next larger item, which can look like a wrong answer rather than an error.
Also check the sixth argument, search_mode. The default 1 searches first to last; -1 searches last to first. If duplicate IDs exist, the two modes return different rows.
Type the arguments explicitly for an assignment so the marker can see your intent: =XLOOKUP(A2,IDs,Names,"Not found",0,1).
6. Wildcards and Partial Matches
XLOOKUP supports wildcards when match_mode is 2. Use * for any number of characters and ? for one character. For example, =XLOOKUP("*"&A2&"*",Names,IDs,"Not found",2) finds a name containing the text in A2.
Wildcard mode is not the same as approximate numeric matching. If you need a tier lookup, use -1 with a sorted lookup array, or build an explicit IFS ladder that a marker can read.
7. Fix the Data Before You Fix the Formula
Most XLOOKUP failures are data failures. Run these checks beside the lookup column:
=ISNUMBER(A2)and=ISNUMBER(B2)— both sides should agree.=LEN(A2)— compare a failing key with a working one; a difference often means a hidden space.=TRIM(A2)— remove leading, trailing and repeated spaces.=COUNTIF(lookup_range,A2)— zero means no match; more than one means duplicates.
The free Data Cleaner strips spaces and non-printing characters from a pasted column. The Formula Checker flags unequal ranges, unsupported functions and risky match modes.
Before You Submit
Test four cases: a normal match, a key with a trailing space, a key that should not exist, and a duplicate key. Confirm the fallback text appears where expected and that no #SPILL! outline overlaps another table. Save a copy and reopen it — spilled arrays can change if the workbook was saved by an older Excel version.
Related guides: numbers stored as text, INDEX and MATCH, and VLOOKUP examples. For a lookup assignment, see Excel data analysis assignment help.
