#N/A Error
Not Available. The lookup found nothing matching your search value. #N/A haunts every VLOOKUP, XLOOKUP, and MATCH formula — and 90% of the time, the fix is one of five specific data hygiene issues.
Signals "not available" — a lookup function couldn't find a match for your search value. Different from other errors: the formula worked correctly, it just didn't find what it was looking for. The value might genuinely not exist, or it might exist but not match due to hidden differences.
What #N/A means
#N/A tells you a lookup function searched through your data and came up empty. VLOOKUP couldn't find the customer ID. XLOOKUP couldn't find the SKU. MATCH couldn't find the region. Unlike #VALUE! (data type) or #REF! (broken reference), #N/A is about the lookup itself — the formula ran, searched, and returned nothing.
The value you're searching for might legitimately not be in the data, in which case #N/A is Excel doing its job. But most of the time, the value IS there — and #N/A appears because of subtle mismatches: extra spaces, different case, text-vs-number type differences, or wrong lookup direction. Fixing #N/A is almost always about aligning your search value with what's actually in the lookup range.
🔍 The #1 diagnostic: does it exist?
Before assuming a mismatch, verify the value exists at all with COUNTIF: =COUNTIF(lookup_range, search_value). If it returns 0, the value truly isn't there — check spelling or source data. If it returns 1+, the value exists but VLOOKUP still can't find it — you have a subtle mismatch (spaces, types, case) to hunt down.
The 5 root causes of #N/A
You searched for customer ID "C-9999" and it's simply not in the customer table. #N/A is correct — the search worked, just found nothing. Confirm with COUNTIF returning 0.
Search value is "Widget" but lookup range contains "Widget " (trailing space) or " Widget" (leading space). Excel treats these as different values. Very common with imported data.
Search value is the number 100. Lookup range has "100" as text. VLOOKUP requires exact type match — number vs text-that-looks-like-number are different. Also common with imported CSVs.
VLOOKUP only searches rightward. If your search value is in column D but the return column is B (to the left), VLOOKUP returns #N/A. Use XLOOKUP or INDEX/MATCH — they search in any direction.
VLOOKUP with TRUE (approximate match) requires the lookup column to be sorted ascending. Unsorted data + approximate match = unpredictable #N/A results. Either sort the column or use exact match (FALSE).
5 real-world fix scenarios
VLOOKUP fails even though the value "obviously" exists
You can see "Widget" in the lookup range and you're searching for "Widget" — but VLOOKUP returns #N/A. Trailing space in the source data.
Best fix: select the lookup column, Data → Text to Columns → Finish. Or write a helper column with TRIM applied.
Numeric IDs stored as text in one place, numbers in another
Search cell has 12345 as a number; lookup range has "12345" as text. Types don't match.
Root fix: unify the data types. Select column D → Data → Text to Columns → Finish converts text-numbers to real numbers.
Search value is right of return value
Product name in column D, product code in column A. Trying to find the code by searching the name — VLOOKUP can't search rightward-then-return-leftward.
Long-term: replace all VLOOKUPs with XLOOKUP. Same syntax simplicity, no direction limitation. Read our XLOOKUP guide.
Show a friendly message instead of #N/A
Sometimes the value legitimately isn't there and you don't want the ugly #N/A displayed. Use IFNA (better than IFERROR — it only catches #N/A, not other errors that might indicate real bugs).
Why IFNA over IFERROR: IFERROR hides ALL errors including #VALUE! and #REF! which indicate real bugs. IFNA only hides #N/A — the "not found" case you actually want to handle silently.
VLOOKUP with TRUE returning wrong values or #N/A
You forgot the FALSE argument, so VLOOKUP defaults to approximate match. Data isn't sorted. Results are chaotic.
Always specify: the fourth argument is technically optional but defaults to TRUE (dangerous). Always write FALSE explicitly.
Prevention checklist
Build lookups that don't break
- Use XLOOKUP instead of VLOOKUP. No direction limits, better error handling, cleaner syntax. Read our XLOOKUP guide.
- Clean data at import. Power Query (Data → Get Data) applies TRIM and type coercion during import, preventing most mismatches.
- Standardize your source tables. Convert to Excel Tables. Apply TRIM in a helper column. Store IDs as numbers OR text consistently — not both.
- Test with COUNTIF first. Before debugging a mysterious #N/A, verify the value exists at all with
=COUNTIF(range, value). Saves hours. - Always specify exact match. Write FALSE explicitly in VLOOKUP. Never rely on the default.
- Use IFNA at the display layer. Wrap lookups with IFNA to show friendly "Not found" messages instead of #N/A in reports.
- Add data validation to search cells. Restrict search values to items from a dropdown — impossible to search for something not in the list.
#N/A vs other errors
Version compatibility
#N/A behaves identically across all Excel versions. IFNA function requires Excel 2013+.
Related errors and resources
Frequently asked questions
Should I use IFERROR or IFNA to hide #N/A?
Use IFNA. IFERROR catches EVERY error including #VALUE!, #REF!, and #DIV/0! — all of which indicate real problems you probably want to see. IFNA only catches #N/A, letting other errors surface so you can fix them. IFNA is the right tool for lookups; IFERROR is a hammer for everything.
Why does COUNTIF find the value but VLOOKUP doesn't?
Because COUNTIF is more forgiving — it does implicit type coercion in many cases. VLOOKUP with exact match is stricter about type differences. If COUNTIF returns 1 but VLOOKUP returns #N/A, you have a subtle mismatch: usually text-vs-number types or trailing spaces. Wrap both sides in TRIM and try again.
How do I VLOOKUP leftward?
You can't with VLOOKUP alone. Use XLOOKUP (built for this) or INDEX/MATCH: =INDEX(return_col, MATCH(search, search_col, 0)). INDEX/MATCH works in any direction. XLOOKUP is cleaner and does the same thing.
Can #N/A appear in non-lookup formulas?
Yes. NA() function returns #N/A intentionally. Chart source data sometimes uses NA() to skip missing points. Any formula referencing a cell containing #N/A propagates the error unless caught with IFNA.
Why do numbers sometimes fail lookup even when they look identical?
Because one is a number and the other is text. In Excel, the number 100 and the text "100" are different values. Look at the source cells: numbers are right-aligned by default, text is left-aligned. If your search value and source data align differently, that's your problem.
Should I let #N/A show or always hide it?
Depends on audience. For your own development work, let #N/A show — it's diagnostic information. For dashboards viewed by executives or customers, wrap in IFNA to show a clean "N/A" or empty string. Never hide #N/A during data quality auditing.
Can wildcards fix #N/A when the search value is partial?
Yes. VLOOKUP with FALSE supports wildcards: =VLOOKUP("*Widget*", table, 2, FALSE) matches any value containing "Widget". Use * for any characters, ? for one character. Powerful for fuzzy matching.
Fix every #N/A in under a minute.
The Add-in scans your lookup formulas, identifies why each #N/A is happening (spaces, types, direction), and rewrites the formulas to be robust.
Get the Add-in →