How to Fix the #N/A Error in Excel & Google Sheets

The #N/A error stands for “No Value Available”. It is the most common spreadsheet error and indicates that a lookup function (VLOOKUP, XLOOKUP, MATCH, HLOOKUP) searched for a key and could not find an exact match in the reference table.

---

🔍 Diagnostic Checklist: The 4 Common Causes

Cause 1: Hidden Spaces (The #1 Culprit)

Even if "Acme Corp" looks identical in both sheets, a trailing space ("Acme Corp ") will cause an instant #N/A.

• Fix: Wrap your lookup value or table column with TRIM:

  =XLOOKUP(TRIM(A2), TRIM(SourceTable[Company]), SourceTable[Revenue])

---

Cause 2: Text vs. Number Data Type Mismatch

If the lookup cell is formatted as Text ('1042) but the table column contains Numbers (1042), Excel will not match them.

• Fix (Convert Text to Number): Multiply by 1 (A2 * 1) or use VALUE(A2).
• Fix (Convert Number to Text): Concatenate with an empty string (A2 & "") or use TEXT(A2, "@").

---

Cause 3: VLOOKUP Missing FALSE

If you omit the 4th argument in VLOOKUP, it defaults to TRUE (approximate match) and returns #N/A if the lookup column isn’t sorted in strict ascending order.

• Fix: Always specify FALSE (or 0):

  =VLOOKUP(A2, $B$2:$E$100, 3, FALSE)

---

Cause 4: Graceful Error Masking

If items genuinely might not exist in the catalog and you want a clean report:

• In XLOOKUP:

  =XLOOKUP(A2, Products!A:A, Products!B:B, "Not in Catalog")

• In VLOOKUP:

  =IFNA(VLOOKUP(A2, Products!A:D, 2, FALSE), "Not in Catalog")