← Back to Error Directory

VLOOKUP Returns #N/A on Values That Look Identical? Here's the Real Fix

The most frustrating VLOOKUP failure — two cells that look exactly the same, but refuse to match. Here's what's actually different between them.

Table of Contents

1. Why "They Look the Same" Doesn't Mean "They Are the Same"

This is one of the most common support questions in any spreadsheet forum: "My VLOOKUP formula is correct, the value clearly exists in the lookup range, and it still returns #N/A." Nine times out of ten, the two values are not byte-for-byte identical, even though your eyes can't tell the difference. VLOOKUP performs an exact character-by-character comparison (unless you're using approximate match, which has its own separate issues) — it has no concept of "close enough" or "looks the same to a human."

2. Interactive Demo: Hidden Character Detector

Paste two values below that look identical to you — this reveals exactly what's different at the character level, the same way VLOOKUP "sees" them.

The default example has a trailing space in "Chicago " — this is invisible to the eye but breaks the match completely. Try pasting a value with a non-breaking space (common from copied web tables) to see it flagged specifically.

3. The Five Real Causes

  1. Trailing or leading spaces
  2. Non-breaking spaces (character code 160) from pasted web or PDF content
  3. Text-formatted numbers being compared against true numeric values
  4. AutoCorrect silently changing a character (like a straight apostrophe to a curly one)
  5. Case-sensitivity misconceptions (this one is actually a myth — see section 7)

4. Trailing and Leading Spaces

The single most common cause, especially with data exported from another system or typed manually with an accidental extra keystroke. Fix using TRIM() on both sides of the comparison:

=VLOOKUP(TRIM(A2), TRIM(DataRange), 2, FALSE)

Note that TRIM only needs to wrap the lookup range if you can't apply it as an array — in modern Excel with dynamic arrays, wrapping the entire range works directly; in older versions, you may need to first clean the source data with a helper column using TRIM, then reference that cleaned column instead.

5. Non-Breaking Spaces From Web/PDF Data

This is the sneakiest version of the spacing problem, because TRIM() alone does not remove non-breaking spaces (Unicode character 160, often written as   in HTML) — TRIM only removes regular spaces (character 32). Data copied from websites, PDFs, or certain database exports frequently contains these invisible characters:

=VLOOKUP(SUBSTITUTE(TRIM(A2),CHAR(160)," "), DataRange, 2, FALSE)

This formula first replaces any non-breaking space with a regular space, then trims the result — catching both types of invisible whitespace in a single pass.

6. Text-Formatted Numbers vs True Numbers

A number stored as text (often from a CSV import or a system export that formats IDs as text to preserve leading zeros) looks visually identical to a genuine number but is a completely different data type internally. VLOOKUP will not match "1023" (text) against 1023 (number):

=VLOOKUP(VALUE(A2), NumericDataRange, 2, FALSE)     — if A2 is text but the range is numeric
=VLOOKUP(TEXT(A2,"0"), TextDataRange, 2, FALSE)      — if A2 is numeric but the range is text

A quick way to check which situation you're in: numbers are right-aligned by default in a cell, while text is left-aligned. If your lookup value and your table's key column are aligned differently, that's a strong signal of a type mismatch.

7. Case Sensitivity Misconceptions

A common false assumption: that "chicago" won't match "Chicago" because of letter case. This is actually incorrect — standard VLOOKUP is case-insensitive by default and will match regardless of capitalization. If you need genuinely case-sensitive matching (rare, but occasionally needed for things like case-sensitive product codes), you'd need EXACT() combined with an array formula or INDEX/MATCH instead, since standard VLOOKUP cannot enforce case sensitivity at all.

8. AutoCorrect-Altered Characters

Excel and Google Sheets both apply AutoCorrect to typed text, which can silently convert a straight apostrophe (') into a curly one ('), or hyphens into en-dashes, depending on your settings. If one value was typed directly and another was pasted from a source that used the "correct" character already, they may look pixel-identical on screen but differ at the character level. Disable AutoCorrect for the relevant characters (File → Options → Proofing → AutoCorrect Options) if this becomes a recurring issue in your data entry process.

9. The Universal Bulletproof Fix

When you're not sure which of the above is causing your specific mismatch, this combined formula defends against the three most common invisible culprits simultaneously:

=VLOOKUP(TRIM(SUBSTITUTE(A2,CHAR(160)," ")), DataRange, 2, FALSE)

Apply the identical cleaning logic to your source data's key column (via a helper column) if the problem persists, since the mismatch could be coming from either side of the comparison, not just your lookup value.

10. Frequently Asked Questions

Why does VLOOKUP return #N/A when the values look the same?

This almost always means the two values are not actually identical at the character level, even though they appear the same visually. The most common causes are trailing or leading spaces, non-breaking spaces from pasted web or PDF data, or a text-formatted number being compared against a true numeric value.

How do I remove hidden spaces before using VLOOKUP?

Wrap your lookup value and the lookup range in the TRIM function to remove regular leading and trailing spaces, and use SUBSTITUTE with CHAR(160) to remove non-breaking spaces that TRIM alone cannot catch.

Why does VLOOKUP fail on numbers that look identical?

This typically happens when one value is stored as actual numeric data and the other is stored as text that merely displays the same digits. VLOOKUP treats these as fundamentally different data types and will not match them without an explicit conversion using VALUE or by multiplying by 1.

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.