XLOOKUP Not Working in Excel? Fix #N/A, #VALUE!, and #SPILL!

When XLOOKUP is not working, the formula syntax is often fine. The usual problem is that the lookup value does not exactly match the lookup array, the lookup and return ranges have different sizes, the result cannot spill into nearby cells, or the formula was copied with the wrong references.

Start with this safe pattern:

=XLOOKUP(F2,$A$2:$A$100,$C$2:$C$100,"Not found")

Then use the symptom table below to find the right fix.

1. Quick Diagnosis Table

2. Check the Formula Structure First

XLOOKUP separates the value to find, the column to search, and the column to return from. It does not use a column number like VLOOKUP:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

For most lookups, use the first four arguments:

=XLOOKUP(F2,A2:A100,C2:C100,"Not found")

If the formula returns the wrong field, check that the return array is the column you intended. XLOOKUP will not protect you from selecting a valid but incorrect return range.

3. Fix XLOOKUP #N/A

#N/A means XLOOKUP did not find an exact match in the lookup array. The formula can be structurally correct while the source data is inconsistent.

Check for Extra Spaces

Use LEN to compare the number of characters:

=LEN(F2)

If the lookup value came from a website, PDF, or CSV file, remove regular and non-breaking spaces:

=TRIM(SUBSTITUTE(F2,CHAR(160)," "))

Clean the lookup column and the value being searched. Cleaning only one side does not fix the mismatch.

Check Numbers Stored as Text

The number 1001 and the text "1001" may look the same but are different values to Excel. Test the type on both sides:

=ISNUMBER(F2)

=ISTEXT(F2)

Convert the lookup value when the source column contains numbers:

=XLOOKUP(VALUE(F2),$A$2:$A$100,$C$2:$C$100,"Not found")

Use COUNTIF as a quick presence check:

=COUNTIF($A$2:$A$100,F2)

If the result is 0, the exact value is not in the lookup array. If it should be greater than 0, inspect spaces, hidden characters, and data types.

4. Fix XLOOKUP #VALUE!

#VALUE! commonly appears when the lookup array and return array do not have matching dimensions.

This formula is invalid because the arrays have different row counts:

=XLOOKUP(F2,A2:A100,C2:C90,"Not found")

Make the ranges the same size:

=XLOOKUP(F2,A2:A100,C2:C100,"Not found")

The same rule applies when searching horizontally. The lookup and return arrays must be compatible one-dimensional ranges.

5. Fix XLOOKUP #SPILL!

XLOOKUP can return multiple columns. When that happens, the result needs empty cells next to the formula:

=XLOOKUP(F2,$A$2:$A$100,$C$2:$D$100,"Not found")

This formula returns both columns C and D. If either output cell is occupied, Excel may show #SPILL!.

Check these points:

1. Clear values, formulas, and merged cells in the expected output area.
2. Make sure the formula is not inside an Excel Table that blocks spilling.
3. Check whether another dynamic-array formula already uses the same cells.

See Excel Dynamic Array Formulas for more detail about spilled results.

6. Fix Wrong Results

XLOOKUP uses exact match by default, but you can change the behavior with match_mode.

For ordinary business data, make exact matching explicit when debugging:

=XLOOKUP(F2,$A$2:$A$100,$C$2:$C$100,"Not found",0)

If duplicate keys exist, XLOOKUP returns the first match. To return the last match instead, use search_mode -1:

=XLOOKUP(F2,$A$2:$A$100,$C$2:$C$100,"Not found",0,-1)

If you need to review duplicate records first, use How to Find Duplicates in Excel.

7. Fix Formulas That Break After Copying

When you copy a formula down, relative references move. Lock the lookup and return arrays when they should remain fixed:

=XLOOKUP(F2,$A$2:$A$100,$C$2:$C$100,"Not found")

Here, F2 changes to F3, F4, and so on, while the source ranges stay fixed. If you are unsure which dollar signs to use, review Excel cell references.

8. Check Excel Version Compatibility

If Excel does not recognize XLOOKUP at all, the workbook may be opened in a version that does not support it. XLOOKUP is intended for modern Excel versions, including Microsoft 365 and Excel 2021 or later.

Compare the tradeoffs in VLOOKUP vs XLOOKUP vs INDEX MATCH.

9. Use if_not_found Carefully

The fourth argument can replace #N/A with a friendly message:

=XLOOKUP(F2,$A$2:$A$100,$C$2:$C$100,"Not found")

This is useful when a missing value is expected. During debugging, temporarily remove the custom message so you can distinguish a real missing match from a formatting problem.

Do not wrap every XLOOKUP in IFERROR before identifying the cause. A broad error wrapper can hide #VALUE! and #SPILL! problems that still need fixing.

10. Troubleshooting Checklist

When XLOOKUP fails, check the formula in this order:

1. Is the lookup value actually present in the lookup array?
2. Are both sides using the same data type?
3. Do extra spaces or hidden characters exist?
4. Do the lookup and return arrays have matching dimensions?
5. Are the output cells empty if the formula returns multiple columns?
6. Are the source ranges locked before copying the formula?
7. Is the selected match mode intentional?
8. Does the user's Excel version support XLOOKUP?

11. FAQ

Why is XLOOKUP not working even though the values match?

The values may only look identical. Check for leading or trailing spaces, non-breaking spaces, and numbers stored as text. Use LEN, TRIM, ISNUMBER, and ISTEXT to compare the two values.

Why does XLOOKUP return #VALUE!?

The lookup and return arrays usually have different dimensions. Make both ranges the same height or width, then check that the return range is the one you meant to use.

Why does XLOOKUP return #SPILL!?

The formula is returning more than one cell, but the output area is blocked. Clear the cells to the right or below the formula and check for merged cells.

Why does XLOOKUP return the wrong row?

Check the match mode, duplicate keys, and search mode. For ordinary IDs and names, use exact match mode 0, and use search mode -1 only when you intentionally want the last match.

12. Related Guides

• Learn the full function in the Excel XLOOKUP tutorial.
• Compare lookup choices in VLOOKUP vs XLOOKUP vs INDEX MATCH.
• Fix the VLOOKUP version of the problem with VLOOKUP troubleshooting.
• Review Excel formula errors for #REF!, #VALUE!, and other formula problems.
• Use Excel FILTER vs VLOOKUP when the task requires returning multiple matching rows.