#N/AExcel error guide No upload

#N/A in Excel — Lookup value not found

#N/A means a lookup or match function could not find the value. The value may be missing, spelled differently, stored as text instead of a number, or surrounded by spaces. Use exact-match mode and return a clear fallback.

Reviewed 31 October 2026 Tested in Excel 365; fixes apply to 2021, 2024 and Mac where the feature exists

The short answer

What it looks like

VLOOKUP, XLOOKUP, MATCH or INDEX/MATCH returns #N/A
Only some rows fail while the lookup table looks correct
The lookup works when you type the value manually

Why #N/A happens

The lookup value does not exist in the lookup column.
One value is stored as text and the other as a number (for example, 1001 vs "1001").
Leading or trailing spaces, non-breaking spaces or inconsistent capitalisation.
VLOOKUP is using approximate match because the fourth argument is omitted or TRUE.
The lookup column is not the first column of the table_array in VLOOKUP.
A merged cell, hidden row or filtered range is hiding the real match.

How to fix #N/A

1
Force an exact match
VLOOKUP defaults to approximate match when the fourth argument is missing. Add FALSE. XLOOKUP is exact by default, but check its match_mode argument if you changed it.
```
=VLOOKUP(A2,$D$2:$F$500,3,FALSE)
```
2
Normalise both sides of the lookup
Trim spaces and align the data type. If one side is text, convert it with VALUE or convert both sides to text with TEXT.
```
=XLOOKUP(TRIM(A2),TRIM($D$2:$D$500),$B$2:$B$500,"Not found")
```
3
Return a useful fallback
IFNA catches only #N/A, so genuine syntax or reference errors still surface. Use it when a missing lookup is a normal business case.
```
=IFNA(VLOOKUP(A2,$D$2:$F$500,3,FALSE),"Not found")
```
4
Check for hidden characters
Use LEN to compare the visible text with its stored length. A value that looks like 8 characters but has LEN 9 usually contains a trailing space or non-breaking space.
```
=LEN(A2)
```

A safer formula pattern

=IFNA(XLOOKUP(TRIM(A2),TRIM($D$2:$D$500),$B$2:$B$500),"Not found")

Exact lookup with trimmed input and a controlled fallback; only #N/A is replaced.

Check it in your own workbook

Paste the formula into the free browser tools to see the exact fix without uploading your file. The Error Fixer explains the code, the Formula Checker finds syntax problems, and the Formula Explainer breaks down nested logic.

Open the interactive Error Fixer Check a formula

Related functions

VLOOKUP XLOOKUP MATCH IFNA TRIM

Frequently asked questions

What does #N/A mean in Excel?

How do I fix #N/A in Excel?

Force an exact match: VLOOKUP defaults to approximate match when the fourth argument is missing. Add FALSE. XLOOKUP is exact by default, but check its match_mode argument if you changed it. Normalise both sides of the lookup: Trim spaces and align the data type. If one side is text, convert it with VALUE or convert both sides to text with TEXT.

Why does #N/A appear only in some rows?

Usually because only some rows contain the trigger: The lookup value does not exist in the lookup column. One value is stored as text and the other as a number (for example, 1001 vs "1001"). Leading or trailing spaces, non-breaking spaces or inconsistent capitalisation.

How can I stop #N/A from appearing again?

Exact lookup with trimmed input and a controlled fallback; only #N/A is replaced. Use this pattern as a guard: =IFNA(XLOOKUP(TRIM(A2),TRIM($D$2:$D$500),$B$2:$B$500),"Not found")

Related Excel error guides

All error codes →

#VALUE!Wrong value type#VALUE! means Excel expected a number, date or logical value but found text, an empty cell or a value from another data type. The fix is to convert the input, remove hidden characters, or guard the calculation with IFERROR.#REF!Broken cell reference#REF! means a formula points to a cell, row, column or sheet that no longer exists. It usually appears after deleting rows or columns, moving a sheet, or pasting a formula into a workbook that does not contain the referenced sheet.#DIV/0!Division by zero#DIV/0! appears when a formula divides by zero or by an empty cell. Excel treats a blank denominator as zero. Check the denominator, exclude blank rows, or return a controlled blank with IFERROR.#NAME?Unknown function or name#NAME? means Excel does not recognise a function, named range or reference in the formula. The usual causes are a misspelled function, missing quotation marks around text, or a function that is not available in your Excel version.

Still stuck?

Send the workbook and get the formula fixed

A human Excel expert can repair the formula, explain the cause and return a tested workbook — usually within 24 hours. Fixed quote before work starts, from $15/hr.

Get a fixed quote Ask on WhatsApp

#N/A in Excel — Lookup value not found

Reviewed 31 October 2026 Tested in Excel 365; fixes apply to 2021, 2024 and Mac where the feature exists

Why #N/A happens

The lookup value does not exist in the lookup column.

One value is stored as text and the other as a number (for example, 1001 vs "1001").

Leading or trailing spaces, non-breaking spaces or inconsistent capitalisation.

VLOOKUP is using approximate match because the fourth argument is omitted or TRUE.

The lookup column is not the first column of the table_array in VLOOKUP.

A merged cell, hidden row or filtered range is hiding the real match.

How to fix #N/A

Force an exact match

VLOOKUP defaults to approximate match when the fourth argument is missing. Add FALSE. XLOOKUP is exact by default, but check its match_mode argument if you changed it.

=VLOOKUP(A2,$D$2:$F$500,3,FALSE)

Normalise both sides of the lookup

Trim spaces and align the data type. If one side is text, convert it with VALUE or convert both sides to text with TEXT.

=XLOOKUP(TRIM(A2),TRIM($D$2:$D$500),$B$2:$B$500,"Not found")

Return a useful fallback

IFNA catches only #N/A, so genuine syntax or reference errors still surface. Use it when a missing lookup is a normal business case.

=IFNA(VLOOKUP(A2,$D$2:$F$500,3,FALSE),"Not found")

Check for hidden characters

Use LEN to compare the visible text with its stored length. A value that looks like 8 characters but has LEN 9 usually contains a trailing space or non-breaking space.

=LEN(A2)