Fix the #N/A Error

Excel & Google Sheets

How it works

#N/A literally means "no match found." Either the value genuinely isn't in the lookup column, or — far more often — it is there but doesn't match exactly: a trailing space, a number stored as text, or different capitalization of an ID. First diagnose which case you have. If missing values are expected and you just want a clean sheet, wrap the lookup in IFERROR to show a friendly message instead. But don't reach for IFERROR first — it hides real data problems as happily as expected ones.

VLOOKUP(A2,Sheet2!A:B,2,FALSE)

The original lookup, unchanged.

IFERROR(…, "Not found")

If the lookup errors, show "Not found" instead of #N/A.

When to use it

Use IFERROR when missing matches are legitimate — new records, optional data. Fix the underlying data instead when every value should have a match.

Common mistakes

• ✕Invisible spaces in the lookup value or table.

  ✓"E-104 " won't match "E-104". Test with =TRIM(A2)=TRIM(Sheet2!A5), then clean both sides with TRIM.

• ✕Numbers stored as text on one side.

  ✓The number 104 never matches the text "104". Convert with VALUE(), or multiply the text column by 1.

• ✕Wrapping everything in IFERROR to make errors disappear.

  ✓IFERROR also hides typos and broken references. Diagnose first; suppress only expected misses.