#N/A Error in Excel — Complete Fix Guide

Q: How do I stop #N/A in XLOOKUP?

Always supply the if_not_found argument. Example: =XLOOKUP(E2,A:A,B:B,"Not found"). This returns your custom message instead of #N/A when the key is missing.

When a lookup cannot find the value — every real cause and the exact formulas that stop #N/A permanently.

1. What #N/A Actually Means
2. Interactive Demo: Lookup Failure Checker
3. Cause 1: The Lookup Value Simply Is Not There
4. Cause 2: Trailing Spaces & Non-Breaking Spaces
5. Cause 3: Text vs Number Mismatch
6. Cause 4: Approximate Match on Unsorted Data
7. Cause 5: FILTER Returns No Rows
8. Defensive Coding Pattern
9. Frequently Asked Questions

1. What #N/A Actually Means

Excel returns #N/A (Not Available) when a lookup or match function cannot locate the requested value. It is the engine’s way of saying “I searched and found nothing.” The error is raised by VLOOKUP, HLOOKUP, XLOOKUP, MATCH, XMATCH, LOOKUP, and by FILTER when no rows satisfy the criteria and no if_empty argument is supplied.

Unlike #DIV/0! or #VALUE!, #N/A is often expected in real models (a product code that has not been ordered yet, a customer that has not purchased this month). The goal is therefore not always to eliminate every #N/A, but to control what the user sees when a match is legitimately missing.

2. Interactive Demo: Lookup Failure Checker

See how different formulas react when the key is missing or mistyped.

Formula under test:

3. Cause 1: The Lookup Value Simply Is Not There

The simplest case — the key does not exist in the lookup column. Always give XLOOKUP an if_not_found value:

=XLOOKUP(E2,A:A,B:B,"Not found")

' Classic VLOOKUP equivalent:
=IFNA(VLOOKUP(E2,A:B,2,FALSE),"Not found")

For multi-criteria lookups the same principle applies — wrap the whole expression in IFNA or supply a fallback.

4. Cause 2: Trailing Spaces & Non-Breaking Spaces

Data imported from web pages, ERP exports or PDFs frequently contains trailing spaces or the non-breaking space character CHAR(160). The value looks identical on screen but fails an exact match:

' Clean both sides
=XLOOKUP(TRIM(E2),TRIM(A2:A100),B2:B100)

' Also strip non-breaking spaces
=XLOOKUP(
  TRIM(SUBSTITUTE(E2,CHAR(160),"")),
  TRIM(SUBSTITUTE(A2:A100,CHAR(160),"")),
  B2:B100
)

A quick diagnostic: =LEN(A2) versus the number of visible characters. If they differ, invisible characters are present.

5. Cause 3: Text vs Number Mismatch

One column stores “12345” as text, the other stores 12345 as a number. Exact-match lookups fail even though the digits look the same:

' Force both sides to numbers
=XLOOKUP(--E2,--A2:A100,B2:B100)

' Or both sides to text
=XLOOKUP(E2&"",A2:A100&"",B2:B100)

You can also fix the source data once with Text to Columns or by multiplying the whole column by 1.

6. Cause 4: Approximate Match on Unsorted Data

VLOOKUP’s fourth argument defaults to TRUE (approximate match). Approximate match requires the lookup column to be sorted ascending; otherwise you get wrong results or #N/A:

' Always use exact match unless you deliberately need approximate
=VLOOKUP(E2,A:B,2,FALSE)

' Or switch to XLOOKUP (exact by default)
=XLOOKUP(E2,A:A,B:B,"Not found")

7. Cause 5: FILTER Returns No Rows

When no rows satisfy the FILTER criteria, Excel returns #N/A unless you supply the optional if_empty argument:

=FILTER(A2:C100,C2:C100="Open","No open items")

8. Defensive Coding Pattern

Always supply if_not_found (XLOOKUP) or wrap classic lookups with IFNA / IFERROR.
TRIM both the lookup value and the lookup array when data comes from external systems.
Coerce text and numbers to the same type before matching.
Prefer XLOOKUP over VLOOKUP — it is exact by default, can look left, and has a clean not-found argument.
For multi-criteria matches use a Boolean array or a helper column rather than relying on approximate match.

9. Frequently Asked Questions

What causes the #N/A error in Excel?

Excel returns #N/A when a lookup function cannot find the requested value. The most common reasons are a missing key, trailing or non-breaking spaces, text stored as numbers (or vice versa), and using approximate match on unsorted data.

How do I stop #N/A in XLOOKUP?

Always supply the if_not_found argument. Example: =XLOOKUP(E2,A:A,B:B,"Not found"). This returns your custom message instead of #N/A when the key is missing.

Why does VLOOKUP return #N/A even when the value is visible?

Almost always a data-type mismatch or hidden characters. One side is text, the other is a number, or one side has trailing spaces or CHAR(160) non-breaking spaces. Coerce both sides and TRIM them.

Related Guides

Editorial Disclaimer: This guide is developed to the best of our domain knowledge and tested against Excel 2019+, Microsoft 365, and Google Sheets. Content is updated continuously as spreadsheet calculation engines evolve.