Fix the #N/A error

Excel & Google Sheets

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

The lookup value genuinely does not exist in the lookup_array — a typo, a changed ID, or a filter hiding the row.
A data-type mismatch: the lookup_array contains text representations of numbers ('123') while lookup_value is a real number 123, or the reverse.
The lookup_array and return_array are different sizes — XLOOKUP requires them to be the same length (or same number of rows/columns depending on orientation).
The match_mode argument is set to 1 or -1 (approximate match) but the lookup_array is not sorted in the required order, causing XLOOKUP to stop at the wrong position.
Hidden or filtered rows contain the value but the formula references a different worksheet region or a named range that excludes those rows.

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

Cell row 1 column 1	Cell row 1 column 2
Cell row 2 column 1	Cell row 2 column 2
Cell row 3 column 1	Cell row 3 column 2
Cell row 4 column 1	Cell row 4 column 2

Formula

Runs server-side · free · no signup

How to fix it

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.
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.
3Verify array lengths match: =ROWS(D2:D100) should equal =ROWS(E2:E100) for a vertical lookup. Mismatches cause #N/A in some versions.
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).
5Strip hidden spaces using TRIM on the lookup value: =XLOOKUP(TRIM(A2),D:D,E:E).

Stop hunting errors by hand.

Upload your spreadsheet and the Auditor flags every #N/A and broken formula at once — or paste this one formula and get the fix explained.

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.

Related formulas

XLOOKUP VLOOKUP INDEX / MATCH IFERROR IFNA TRIM

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

Last reviewed: May 28, 2026