Why VLOOKUP Says Not Found When the Values Look Identical

Compare the lengths. In a spreadsheet, put the LEN function next to each value. If two cells that look the same have different lengths, there is something extra in one of them. Then reveal what it is. In Excel you can use the CODE or UNICODE function on the first or last character to see its number, for example UNICODE(RIGHT(A2,1)). A plain space is 32. Anything else is a clue.

• ▸Trailing and leading spaces. The easiest case, and the one TRIM fixes.
• ▸Double spaces inside text. TRIM collapses these to single spaces too.
• ▸Non-breaking space, character 160. It looks like a space but TRIM does not remove it. It arrives from web pages and many exports.
• ▸Zero width space, character 8203, and other invisible characters such as the byte order mark. They have no width at all and survive both TRIM and CLEAN.
• ▸Tabs and line breaks inside a cell, from pasted data. CLEAN removes the first 32 control characters, but leaves the non-breaking space alone.
• ▸Numbers stored as text. A value displayed as 1001 can be the text 1001, which does not match the number 1001.
• ▸Different Unicode forms. An accented letter can be stored as one character or as a letter followed by a combining accent, and they look identical.
• ▸Curly versus straight apostrophes and quotes in names such as O'Brien.

• ▸Excel, standard cleanup: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) replaces non-breaking spaces and then trims.
• ▸Add CLEAN for line breaks and tabs: =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))).
• ▸Zero width space: wrap another SUBSTITUTE with UNICHAR(8203) and an empty string.
• ▸Text to number: multiply by 1 or use VALUE, and the other way around, use TEXT or the ampersand with an empty string, so both sides of the lookup have the same type.
• ▸Google Sheets can do the whole job with REGEXREPLACE, for instance replacing the character class of non-breaking and zero width spaces with an ordinary space or nothing.

Do the cleanup in a helper column, then copy and paste as values into the lookup key. Keep the original column until you have checked the result.

For a long list, paste the whole column into the Whitespace Remover. It trims every line, collapses repeated spaces, converts tabs and replaces non-breaking and zero width spaces in one pass. Paste the cleaned column back and run the lookup again. If matches still fail, compare lengths once more on a failing pair and look at the code of the first character that differs.

• ▸Normalize both sides, not just one. If the lookup column and the lookup value each contain different problems, cleaning only one will not help.
• ▸Use exact match mode for lookups, and wrap the key in the same cleaning formula when you cannot change the source data.
• ▸Use unique IDs, not names, whenever you can. Names collide and vary. IDs rarely have these problems.
• ▸Check for leading zeros in codes: 00123 and 123 are different values unless you have chosen to treat them the same way.

Where the data enters your system, trim and normalize on input. Web forms should trim spaces before saving. Imports should convert non-breaking spaces. Databases can add a constraint that rejects values with leading or trailing spaces. Fixing the source once is cheaper than cleaning every report.