FormulaCraft

Fix the #N/A error

Excel & Google Sheets
Topic:XLOOKUP

What #N/A means

XLOOKUP returns #N/A when the lookup value is not found in the lookup array and no if_not_found argument was provided. Unlike VLOOKUP, XLOOKUP supports a built-in not-found value, but if that argument is omitted and the match fails, the error propagates to the cell.

Common causes

Example fix

Broken
=XLOOKUP(A2,D2:D100,E2:E100)
Fixed
=XLOOKUP(TRIM(A2),D2:D100,E2:E100,"Not found",0)

Added TRIM to remove hidden spaces from the lookup value, specified exact match mode (0), and added a not-found string to distinguish genuine misses from bugs.

Try it with your data

Edit the grid or formula, then run it through a real spreadsheet engine — no signup.

Sample data — click any cell to edit

Runs server-side · free · no signup

How to fix it

  1. 1Use XLOOKUP's built-in not-found argument to confirm whether the issue is a genuine miss: =XLOOKUP(A2,D:D,E:E,"NOT FOUND"). If you see NOT FOUND, the value is truly absent — investigate source data.
  2. 2Check data types: select a cell in the lookup_array and look at the format in the toolbar. If numbers are stored as text, convert them with Paste Special > Multiply by 1 in Excel or VALUE() in Sheets.
  3. 3Verify array lengths match: =ROWS(D2:D100) should equal =ROWS(E2:E100) for a vertical lookup. Mismatches cause #N/A in some versions.
  4. 4For approximate lookups, ensure the lookup_array is sorted ascending for match_mode 1 or descending for match_mode -1. For unsorted data, use match_mode 0 (exact match).
  5. 5Strip hidden spaces using TRIM on the lookup value: =XLOOKUP(TRIM(A2),D:D,E:E).

Got the same error in multiple cells? Upload your whole sheet and the Auditor will flag every #N/A and broken formula at once — free. Pro plans (₹199/$4.99/mo) can apply the verified fixes and download the corrected file in one click.

Frequently asked

How is XLOOKUP #N/A different from VLOOKUP #N/A?

Mechanically the same — the value was not found. But XLOOKUP lets you specify an if_not_found value as the fourth argument, so you can return a blank or custom text instead of #N/A without wrapping the whole formula in IFERROR.

Can XLOOKUP match wildcards?

Yes. Set match_mode to 2 for wildcard matching. Use * for any sequence of characters and ? for a single character in the lookup value.

XLOOKUP is returning #N/A for some rows but not others with the same data — why?

Inconsistent data types are the usual culprit. Some cells in the lookup_array may be formatted as text while others are numbers, even if they look identical. Use ISNUMBER() or ISTEXT() to audit the column.

More on XLOOKUP

See all →
Reference

XLOOKUP

The modern replacement for VLOOKUP and INDEX/MATCH — look up a value and return a match in any direction.

How-to

How to use XLOOKUP with multiple criteria

Concatenate criteria columns in XLOOKUP's lookup array to match on multiple conditions at once, returning results only when all criteria align.

Error fix

Fix #SPILL!

Fix #SPILL! errors from XLOOKUP in Excel — spill range blocked by non-empty cells, merged cells, or returning an array into a table column.

How-to

How to return multiple columns with XLOOKUP

Learn how to use XLOOKUP to fetch data from multiple columns efficiently in both Excel and Google Sheets.

How-to

How to use XLOOKUP with wildcards

Learn how to use XLOOKUP with wildcards to perform flexible lookups in Excel and Google Sheets.

Related formulas

XLOOKUPVLOOKUPINDEX / MATCHIFERRORIFNATRIM

Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.

Last reviewed: