Excel / 5 min read
How to Fix #N/A Error in Excel
Fix #N/A in lookup formulas by checking match values, lookup direction, hidden spaces, and approximate-match settings.
Error text: #N/A
A #N/A error usually means Excel could not find a match. In lookup formulas, the first things to check are mismatched values, hidden spaces, and whether the match type is correct.
Check this first
- Confirm the lookup value really exists in the source range.
- Trim spaces from both the lookup value and source values.
- Use exact match when approximate match is not intended.
- Make sure the lookup column is the correct one for the function you used.
Working examples
Exact-match VLOOKUP
=VLOOKUP(A2, Sheet2!A:B, 2, FALSE)Return blank instead of #N/A after the lookup works logically
=IFNA(VLOOKUP(A2, Sheet2!A:B, 2, FALSE), "")Why #N/A is common in lookups
Lookups fail when the two sides do not match exactly enough. Sometimes the problem is obvious, like a missing ID. Other times it is subtle, such as a text-formatted number, a trailing space, or a lookup using approximate mode by accident.
The fix is usually not to hide the error immediately. First confirm whether the value should exist at all.
The order of checks that saves time
Start by testing the lookup value manually in the source table. If it is there, compare formatting next. If formatting is fine, inspect the match mode and the lookup range direction.
- Test a known existing value first.
- Compare data types on both sides.
- Prefer exact match unless you explicitly need approximate logic.
When to use IFNA
Use IFNA after you have confirmed that missing results are acceptable in the workflow. It is useful in dashboards and optional lookups, but it should not replace debugging when a required record disappears unexpectedly.