Why Two Identical-Looking Lines Won't Match

Q: Why doesn't Excel's exact-match formula work on text that looks identical?

Excel's EXACT() , VLOOKUP() , and = comparisons all check the underlying characters, not how the cell looks. A regular space (character code 32) and a non-breaking space (character code 160) render identically but are different characters, so any exact comparison treats them as different values.

You're staring at two cells, two lines, or two strings that look completely identical - same letters, same spacing, same everything you can see - and whatever you're using to compare them insists they're different. A VLOOKUP returns #N/A. A list comparison puts the same-looking item in both "A Only" and "B Only". A deduplication script keeps both copies. You've checked for typos twice. There aren't any. Here's what's actually going on, roughly in order of how often each one is the culprit.

Quick answer

Two identical-looking pieces of text usually fail to match because of an invisible character - most often a non-breaking space, which renders exactly like a regular space but is a different character underneath. Other common causes are mismatched capitalization, trailing whitespace, or leading zeros. Run the text through an invisible character detector to see exactly what's really there.

1. Invisible characters (the most likely cause)

This is the culprit behind the majority of "but they look identical" cases. A handful of Unicode characters render as blank space, or nothing at all, while being completely different from a regular space or a regular line break underneath:

Non-breaking space (NBSP) - looks exactly like a normal space. Comes from HTML (the   entity), Word, and some mobile keyboards.
Zero-width space - completely invisible, takes up no space at all. Common in text copied from web pages.
Carriage return - part of Windows-style line endings; can leave a trailing invisible character at the end of a line.
Byte order mark (BOM) - sometimes prepended to files by Notepad, Excel, or PowerShell, which silently breaks the first line only.

See exactly what's hiding in your text

Open Invisible Character Detector →

Paste the text in, and every invisible character gets flagged with a visible badge showing exactly what it is and where it is. The tool also gives you a cleaned copy with everything stripped out, ready to paste back into wherever you're comparing it.

2. Capitalization

"Apple" and "apple" look different enough that this one is usually easy to spot, but it's worth ruling out first since it's the quickest check. Most comparison tools treat case as significant by default. If you're using the Compare Two Lists tool, turn off Case Sensitive in Options to treat them as equal.

3. Trailing or leading whitespace

A regular space at the end of a line is easy to miss visually, especially in a spreadsheet cell or a text box where the cursor position doesn't make it obvious. Unlike invisible Unicode characters, this one is at least a normal space - but it still breaks an exact match. Most comparison tools have an "ignore leading/trailing spaces" option specifically for this.

4. Leading zeros

"007" and "7" look like they should be the same value, but as text they're different strings of a different length. This shows up constantly with IDs, zip codes, and product codes that get reformatted differently by different systems (Excel is especially prone to silently dropping leading zeros when it interprets a column as a number).

How to actually diagnose it

Paste both pieces of text into the Invisible Character Detector. This rules in or out every invisible-character cause in one step.
If that comes back clean, paste both into Compare Two Lists and toggle Case Sensitive, Ignore Leading/Trailing Spaces, and Ignore Leading Zeros one at a time to see which one changes the result.
If you're cleaning a whole column or list rather than debugging one pair, run it through the List Data Cleaner instead - it handles all of these at once.

Comparing two full lists, not just one pair?

See the full guide to comparing two lists for differences, matches, and duplicates.

Read the guide →

Frequently Asked Questions

Why does VLOOKUP return #N/A even though the value clearly exists in the table?

If you've already confirmed the value is genuinely there - same spelling, same row - the lookup and source value likely aren't byte-for-byte identical. A hidden non-breaking space, a number stored as text, or a value that isn't in the lookup table's first column all produce #N/A even when the cells look the same. Paste both values into the Invisible Character Detector to rule out the hidden-character cause first, since it's the most common one.

Why does VLOOKUP say two identical-looking cells don't match?

The most common cause is a hidden character - usually a non-breaking space - in one of the cells that isn't visible on screen but makes the exact-match comparison fail. Copy the cell contents into the Invisible Character Detector to see exactly what's actually in there.

Why do two copy-pasted list items look the same but not match?

Text copied from a website, PDF, or formatted document often carries invisible characters along with it - non-breaking spaces are the single most common source, since HTML uses them constantly for layout. The visible text looks normal because these characters render as blank space or nothing at all.

Why doesn't Excel's exact-match formula work on text that looks identical?

Excel's EXACT(), VLOOKUP(), and = comparisons all check the underlying characters, not how the cell looks. A regular space (character code 32) and a non-breaking space (character code 160) render identically but are different characters, so any exact comparison treats them as different values.

I trimmed the spaces and it still doesn't match. What else could it be?

Excel's TRIM() function only removes regular spaces, not non-breaking spaces or zero-width characters - so a NBSP-padded cell can still fail to match after trimming. Run the text through the Invisible Character Detector instead; it catches every type, not just regular spaces.

Can two strings look 100% identical and actually be different?

Yes, and it's more common than most people expect. Zero-width spaces are completely invisible - they take up no space at all - so two strings can be pixel-for-pixel identical on screen while containing a different number of characters underneath.

Is this an Excel-only problem?

No. The same issue shows up in any exact-text comparison - list diffing tools, database lookups, form validation, deduplication scripts, and command-line diff. Anywhere text gets compared character-by-character, invisible characters can cause a false mismatch.

How do I stop this from happening again?

You can't fully prevent invisible characters from entering your data - they come from wherever the data originated (web pages, PDFs, other spreadsheets). What you can do is make cleaning a routine step: run new data through the List Data Cleaner or Invisible Character Detector before comparing or importing it.