#N/ASupported on: Microsoft Excel® & Google Sheets™

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

The #N/A error (Not Available) indicates that a lookup function (such as XLOOKUP, VLOOKUP, HLOOKUP, or MATCH) was unable to find an exact match for the search key within the designated lookup array.

Common Root Causes of #N/A

  • 1The lookup value does not exist in the lookup array or column.
  • 2Hidden trailing whitespace, non-breaking spaces (ASCII 160), or capitalization mismatches between lookup key and table.
  • 3Number stored as text mismatch (e.g., looking up integer 1001 against string '1001').
  • 4VLOOKUP approximate match mode enabled with unsorted source data.

Formula Syntax Comparison

Broken Formula
=VLOOKUP(A2, D2:E100, 2, FALSE)
Corrected Formula
=XLOOKUP(TRIM(A2), TRIM(D2:D100), E2:E100, "Not Found")

Manual Step-by-Step Fix

1

Step 1

Wrap formulas in IFERROR() or IFNA() to output a clean fallback (e.g., =IFNA(XLOOKUP(...), 'Not Found')).

2

Step 2

Standardize both columns using TRIM() and CLEAN() to eliminate invisible padding characters.

3

Step 3

Ensure lookup key types match using EXACT() or VALUE().

Automate this Fix with SheetFactorys

Zero manual debugging required

SheetFactorys utilizes deterministic fuzzy matching and string normalization to reconcile casing and whitespace discrepancies, and injects secure IFNA fallbacks automatically.

Available in Microsoft AppSource & Google Workspace
Install for Excel®Install for Sheets™