SpreadsheetFix

VLOOKUP returns #N/A but the values match: fixing hidden spaces

You look up an invoice number from your bank export in the invoice list, and VLOOKUP returns #N/A even though INV-1001 is plainly there in both sheets. Most of the time the two values aren’t actually the same: one has a space you can’t see at the end, at the start or doubled in the middle, or a non-breaking space that came from a web page or PDF. This guide shows how to confirm that’s the problem, how to clean it with formulas, and how SpreadsheetFix cleans every cell in a file at once.

Updated
Applies to
Excel, Google Sheets, CSV
SpreadsheetFix rule
Free up to 5,000 rows

Why this happens

VLOOKUP, XLOOKUP, MATCH and COUNTIF compare text character by character. “INV-1001” and “INV-1001 ” differ by one character, so the lookup fails, and nothing on screen tells you why. Exports are full of these: fixed-width fields from older accounting systems padded with trailing spaces, names typed with a double space, references copied out of an email or a banking portal.

The most stubborn one is the non-breaking space. Web pages and PDFs use it to keep words together, and it comes along when you copy from them. It looks exactly like a normal space but is a different character, code 160 instead of 32. Excel’s TRIM function only removes code 32, so a cell can go through TRIM and still fail the lookup. Tabs and line breaks pasted into a cell cause the same problem.

Spaces don’t only break lookups. Remove Duplicates treats “Contoso GmbH” and “Contoso GmbH” as two different customers, pivot tables split one supplier into two rows, and an import that matches on customer or invoice number creates a new record instead of updating the existing one.

Fix it by hand

Confirm it’s whitespace

  1. 1

    Compare lengths: =LEN(A2) gives more characters than you can see when something is hidden.

  2. 2

    Test the two cells directly with a formula such as =A2=Sheet2!A2. FALSE on values that look the same confirms they differ.

  3. 3

    Find out what the last character is with =CODE(RIGHT(A2,1)). 32 is a normal space, 160 a non-breaking space, 9 a tab and 10 a line break.

Clean it with formulas

  1. 1

    In a helper column, use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")). SUBSTITUTE turns non-breaking spaces into normal ones, then TRIM removes spaces from both ends and reduces runs of spaces inside the text to one.

  2. 2

    To remove line breaks and other non-printing characters too, wrap it in CLEAN: =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))). CLEAN deletes line breaks rather than turning them into spaces, so words on either side of one can run together.

  3. 3

    Fill down, paste the helper column over the original as values, and do the same in the other sheet: both sides of a lookup need cleaning.

Google Sheets has the same TRIM, CLEAN, SUBSTITUTE and CHAR functions, and its TRIM has the same blind spot for non-breaking spaces.

Fix it with SpreadsheetFix

SpreadsheetFix’s Trim Whitespace rule does the formula work across every text cell in the file. It removes whitespace from both ends of each cell and collapses any run of whitespace inside it to a single space. That covers normal spaces, non-breaking spaces, tabs and line breaks, so a reference that ends in a non-breaking space comes out the same as one typed cleanly.

Clean both files you are matching, the bank export and the invoice list, or the CRM export and the order sheet, and the lookups line up. Every changed cell is highlighted in the preview with its value before and after, so you can see which references were carrying hidden characters. If a column needs its spacing kept, such as a fixed-width code, use the column picker to leave it out.

Trim Whitespace is a free rule for files up to 5,000 rows, and it runs in your browser, so neither file is uploaded.

Choose a fileor drop a CSV, TSV or Excel file here, up to 50 MBProcessed in your browser. Nothing is uploaded.

No file to hand?

The same cleaner is on the Trim Whitespace page.

Before and after

Before
invoice,customer,amount
"INV-1001 ",Northwind Ltd,450.00
" INV-1002","Contoso  GmbH",1200.00
"INV-1003 ",Fabrikam Inc,89.90
After
invoice,customer,amount
INV-1001,Northwind Ltd,450.00
INV-1002,Contoso GmbH,1200.00
INV-1003,Fabrikam Inc,89.90

The space after INV-1003 is a non-breaking space, which TRIM in Excel would leave in place.

What it won’t catch

  • Zero-width characters, such as the zero-width space some websites insert, don’t count as whitespace and aren’t removed. If LEN still reports extra characters after cleaning, find them with =UNICODE(MID(A2,n,1)) for each position n.
  • Line breaks inside a cell are turned into a single space rather than deleted, so a two-line address becomes one line.
  • A lookup can also fail for reasons that have nothing to do with whitespace: a number stored as text on one side and as a number on the other, or a lookup range that doesn’t start at the column you are searching. If the values still don’t match after cleaning, check those next.

Questions

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

Usually because the two values differ by a character you can’t see, most often a trailing space or a non-breaking space. Check with =LEN() or a direct comparison such as =A2=B2, then clean both sides.

Why doesn’t TRIM fix my VLOOKUP?

TRIM only removes the normal space character. Non-breaking spaces copied from web pages and PDFs are a different character, so turn them into normal spaces first with SUBSTITUTE and CHAR(160), then trim.

Does trimming remove the spaces between words?

No. Single spaces between words are kept. Spaces at the start and end are removed, and runs of two or more are reduced to one.

Can I clean only some columns?

Yes. Trim Whitespace applies to every text column by default, and its column picker lets you limit it to the columns you choose.