Excel guide

VLOOKUP Returns #N/A: How to Fix It (Easy Steps)

Last updated: October 16, 2026 · 3 min read

You can see the value in your list, but VLOOKUP says #N/A. In most cases the value is not missing. Excel just sees a small difference you cannot see, like a space at the end. Here are three fixes. Start with Fix 1.

Fix 1

Remove hidden spaces with the free tool (the most common VLOOKUP #N/A fix)

Hidden spaces are the most common reason for #N/A. "Boston " with a space at the end is not the same as "Boston" for Excel.

  1. Open the Clean Whitespace tool.
  2. Choose your Excel file.
  3. Click Run and save the cleaned copy.
  4. Use the cleaned file for your VLOOKUP.

The tool cleans every cell in the sheet in one go. It also removes the hidden spaces that come from websites and PDFs, which the Excel function TRIM does not remove. Your original file stays as it is.

Fix 2

Clean the cells with a formula (TRIM, CLEAN and SUBSTITUTE)

Prefer to stay in Excel? Put this formula in an empty column next to your data and copy it down:

=TRIM(CLEAN(SUBSTITUTE(A2,UNICHAR(160)," ")))

Then copy the new column and paste it back with Paste Special, Values. Do this for both lists: the one you search with and the one you search in.

If your Excel wants a semicolon instead of a comma, use ; between the parts.

Fix 3

Make numbers and text match in your VLOOKUP

A number and the same number stored as text look the same, but Excel does not match them. Numbers that sit on the left side of the cell are often text. Check one cell with this formula:

=ISNUMBER(A2)

TRUE means a real number. FALSE means text. To turn text into a real number, use:

=VALUE(A2)

Make sure both lists use the same type, both numbers or both text. Do Fix 1 first. If the numbers also hold hidden spaces, VALUE shows an error.

VLOOKUP still shows #N/A? Check your formula

  • The last part of the formula must be FALSE. Without it, Excel guesses and can fail.
  • Lock your table with dollar signs: $D$2:$E$500. Otherwise the table moves when you copy the formula down.
  • The value you search for must be in the first column of your table. VLOOKUP cannot look to the left.

A complete formula looks like this:

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

If a value really is missing and that is fine, show your own message instead of the error:

=IFERROR(VLOOKUP(A2,$D$2:$E$500,2,FALSE),"Not found")

VLOOKUP #N/A example: before and after cleaning

The city in column A has a space at the end. The list in column D does not.

You search forYour list hasVLOOKUP result
"Boston " (with a space)"Boston"#N/A
"Boston" (after cleaning)"Boston"East

Same city, same list. Only the hidden space is gone, and VLOOKUP finds the value.

How to remove a VLOOKUP formula in Excel and keep the values

The result is right, but you no longer want the formula? Turn the formulas into plain values:

  1. Select the cells with the VLOOKUP results.
  2. Press Ctrl+C to copy them.
  3. Go to Home, click the arrow under Paste and choose Values (the icon with 123).

The formula is gone and the result stays. Keep a copy of your file first, because the cells no longer update when your list changes.

Fix VLOOKUP #N/A in your whole sheet in one click

The Clean Whitespace tool runs on your own computer. Your file is never uploaded.

Questions about VLOOKUP and #N/A

Why does VLOOKUP show #N/A when the value is in the list?

Usually there is a hidden space or the numbers are stored as text. Excel needs an exact match, so one hidden character is enough to fail.

Does TRIM remove all spaces?

No. TRIM removes normal spaces. Spaces copied from websites and PDFs stay. Use the formula in Fix 2 or the Clean Whitespace tool.

How do I hide #N/A in my sheet?

Fix the cause first. If a missing value is normal, wrap your formula in IFERROR and show a message like Not found.

Why does VLOOKUP fail with numbers?

One list holds real numbers and the other holds text numbers. Use ISNUMBER to check, and VALUE to turn text into numbers.

How do I remove a VLOOKUP formula but keep the result?

Copy the cells, then paste them back as Values. In the Home tab, click the arrow under Paste and choose Values. Only the results stay.

Can XLOOKUP replace VLOOKUP?

XLOOKUP can look in any direction and finds exact matches by default. It still shows #N/A when two values differ by a hidden space, so clean your data first.

More Excel guides

← All Excel guides