Excel #N/A: Check the Lookup Key Before Calling It Missing

An exact lookup returning #N/A does not by itself prove that the business record is absent. Microsoft identifies missing keys, mismatched text/number types and extra spaces among the causes. Start with its N/A diagnostic reference, then inspect the source rather than immediately replacing the error with zero.

Establish the search before interpreting the result

Record the requested key, the exact searched range and the field to return. For VLOOKUP, the key must be in the range’s first column; the return-column number is relative to that range. Use FALSE when requesting an exact match. These rules are documented in Microsoft’s VLOOKUP reference.

Keep the raw lookup visible during diagnosis. If a matching row exists, inspect its return cell too: an error already present there needs a source repair, not a new spelling for the search key.

Worked case: a space is not a missing supplier

On a practice sheet enter these four cells: Maple in A2, 11 in B2, Cedar in A3 and 18 in B3. Put ="Cedar " in D2; the quoted text deliberately has one ordinary space after Cedar.

In E2 enter =VLOOKUP(D2,A2:B3,2,FALSE). The documented expected result is #N/A: the supplied key includes a trailing space that the source key does not contain.

Now inspect D2 rather than adding a duplicate source record. For this controlled example, the space is accidental. Replace D2 with ="Cedar"; the expected result is 18. Change D2 to ="Birch"; the expected result is #N/A, this time because neither source key is Birch. Restore Cedar and confirm 18 again.

Those two failures require different actions. Correct an accidental key mismatch using the authoritative identifier. For a genuinely absent required record, obtain it from the source owner; do not invent an amount or substitute another supplier’s value.

Before adding a presentation fallback

In a real import, check whether numeric-looking identifiers are stored consistently as text or numbers. Changing their appearance is not proof of conversion. Preserve leading zeros where they are part of an identifier, and do not indiscriminately remove spaces that may be meaningful.

Do not switch to approximate matching merely to make the error disappear. That changes the question from the requested exact key. If missing data is an accepted report state, choose a clear label and retain a way to inspect unresolved rows. The existing IFNA guide explains its scoped error-handling behavior and why a fallback cannot prove the key is absent.

Scope and evidence

Checked October 22, 2026 for basic typed formulas in Excel for the web. Microsoft’s web formula overview includes an exact VLOOKUP example. No desktop Text to Columns or auditing commands are assumed. This example uses English function names and comma separators; adapt separators to your locale. Expected matches were independently checked, not executed in Excel. No screenshot, paid edition or account access is claimed.

See VLOOKUP for the complete syntax and the error index for other labels.