Wrap in IFNA for a friendly fallback: Change =VLOOKUP(A2, B:D, 2, FALSE) to =IFNA(VLOOKUP(A2, B:D, 2, FALSE), "Not found") IFNA only catches #N/A, not other errors — safer than IFERROR which hides everything.

Why does #N/A keep appearing?

The lookup value is spelled differently in the source (trailing spaces, different case). The lookup value is stored as text in one place and number in the other. The range does not actually contain the value.

How to prevent #N/A in the future?

Standardize data on entry: TRIM and proper type (text/number). Prefer XLOOKUP over VLOOKUP on Excel 365 and Google Sheets — fewer sharp edges. Use data validation to prevent typos in lookup values.

Spreadsheet error

How to fix the #N/A error

TL;DR

#N/A means the lookup value was not found in the source range. Fix: double-check the lookup value exists and the data types match, or wrap in IFNA to return a friendly default.

What it means

The #N/A error stands for "not available." It appears when a lookup function (VLOOKUP, HLOOKUP, MATCH, XLOOKUP) cannot find the value you asked it to find. It is not a broken formula — it is the formula correctly reporting "this does not exist."

Common causes

•The lookup value is spelled differently in the source (trailing spaces, different case).
•The lookup value is stored as text in one place and number in the other.
•The range does not actually contain the value.
•VLOOKUP has approximate-match (TRUE) but the source is not sorted ascending.
•Array formulas expect a match that is not there.

How to fix it

1.Wrap in IFNA for a friendly fallback

Change =VLOOKUP(A2, B:D, 2, FALSE) to =IFNA(VLOOKUP(A2, B:D, 2, FALSE), "Not found")
IFNA only catches #N/A, not other errors — safer than IFERROR which hides everything.

2.Check for whitespace and type mismatch

Test with =TRIM(A2)=TRIM(B2) to see if whitespace is the issue.
Test with =VALUE(A2)=B2 to see if number-vs-text is the issue.
Fix by applying TRIM/VALUE at ingestion or wrapping the lookup: =VLOOKUP(TRIM(A2), B:D, 2, FALSE).

3.Use XLOOKUP with if_not_found

XLOOKUP has a built-in fourth argument for when value is missing.
=XLOOKUP(A2, B:B, C:C, "Not found")
Cleaner than IFNA+VLOOKUP.

Example

Broken=VLOOKUP(A2, B:D, 2, FALSE)Fixed=IFNA(VLOOKUP(TRIM(A2), B:D, 2, FALSE), "")

Wrapped lookup in TRIM to remove whitespace and IFNA to return empty string instead of #N/A when genuinely missing.

Prevention

Standardize data on entry: TRIM and proper type (text/number).
Prefer XLOOKUP over VLOOKUP on Excel 365 and Google Sheets — fewer sharp edges.
Use data validation to prevent typos in lookup values.

Related errors

#REF!#VALUE!#NAME?

Let FormulaPad debug it for you

Paste your broken formula and the error message — get a diff and an explanation in seconds.

Debug a formula