You have two lists. A name appears on both. You write a lookup to match them, and the spreadsheet returns an error for that row — an error that means not found, about a name you are looking directly at, spelled identically, in two cells side by side.
Nothing is broken. The two cells are genuinely different, and the difference is something you cannot see. This is one of the most common time sinks in office work, and once you know the four or five culprits, it takes about two minutes to fix instead of an afternoon.
What is actually in the cell
Text that has been copied out of an email, a web page, or an exported report almost always carries passengers.
- Trailing spaces. The single most common cause.
Kim MinjiandKim Minjiare different strings, and nothing in the display tells you which one you have. - Non-breaking spaces. Web pages and Word documents use a special space character to stop lines breaking in the wrong place. It looks exactly like a normal space and is not one.
- Zero-width characters. Invisible markers used for text direction and word joining. They survive copy and paste and occupy no width at all.
- Different case.
ACME LtdagainstAcme Ltd. Some functions care and some do not, which is worse than if they all cared. - Curly against straight quotes, and en dashes against hyphens. Word processors substitute these automatically, so a name typed by a person and a name exported by a system will not match.
Confirming it before you fix it
Guessing wastes more time than checking. Three quick tests, in a spare column:
- Compare the lengths.
=LEN(A2)against=LEN(B2). If one is longer than the visible text, you have found a passenger — and the difference tells you how many. - Compare exactly.
=EXACT(A2,B2)returns FALSE where=A2=B2returns TRUE. That gap means the only difference is capitalisation. - Look at the alignment. Numbers that sit on the left of their cells are being stored as text. A lookup between a text "1024" and a numeric 1024 will fail no matter how clean both are.
Why the built-in trim is not always enough
Most spreadsheets have a TRIM function that removes leading and trailing spaces and collapses runs of spaces in the middle. It handles the ordinary case well.
What it does not do is remove a non-breaking space, because that is a different character from the one TRIM is looking for. This is exactly why people conclude the data is clean and the lookup is broken. In Excel the usual workaround is to substitute the character out first — =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) — and CLEAN alongside it for control characters. It works, it is fiddly, and you have to remember it every time.
The faster route: clean the column outside the sheet
For a one-off reconciliation it is usually quicker to take the text out, clean it, and put it back. Copy the column, paste it into our Text Cleaner, and run three operations in order:
- Trim each line — removes leading and trailing whitespace from every row.
- Collapse spaces — turns runs of spaces in the middle into single spaces, so
Acme LtdbecomesAcme Ltd. - Remove duplicate lines, if you are building a reference list rather than aligning two columns of equal length.
That last point matters and is easy to get wrong. If you are cleaning a column that has to line up row-for-row with the rest of a sheet, do not remove duplicates or sort — you will silently shift every row below. Deduplicate only when the list stands alone.
If the mismatch is capitalisation, run the cleaned list through our Case Converter and put both sides in lowercase before comparing. It is blunt, but for matching purposes you usually want a comparison key rather than a display name.
The list is processed in your own browser and never uploaded, which is worth knowing when the column in question is customer emails or staff names.
Do it once, in the right place
The habit worth building is cleaning on the way in. When you paste an export into a working file, clean it immediately, before anyone builds formulas on top of it. Cleaning after the fact means re-checking every calculation that already referenced the dirty version.
Keep the raw paste on its own sheet, untouched, and work from a cleaned copy. When somebody asks in three weeks why a total changed, you will be able to answer.
Common questions
Why does the same list match in one column and not another?
Usually because the two columns came from different places — one typed by a person, one exported by a system. Systems are consistent about their padding and people are not, so a column that came from a single source will often match cleanly while a hand-maintained one will not.
Will trimming damage names that legitimately contain spaces?
No. Trimming removes whitespace only at the start and end of each line. Collapsing spaces affects the middle, but it only reduces runs of two or more to one — a normal single space between first and last name is untouched.
I cleaned the list and the lookup still fails.
Check whether one side is a number stored as text. A lookup compares type as well as content, so a numeric ID in one sheet will not find its text twin in another. Convert one side to match the other, then compare lengths again to confirm nothing else is hiding.
More guides
- How to Tell if a Website Is Really Uploading Your Files
- How Much House Can You Actually Afford?
- How to Make a Passport Photo From a Phone Picture
- How to Send Only the Pages You Need From a PDF
- How to Merge Scanned Pages Without the Order Going Wrong
- What You're Actually Pasting Into an Online JSON Formatter
- Five Regex Patterns That Look Right and Quietly Aren't
- camelCase, snake_case or kebab-case: Which Goes Where
- Asking AI for the First Time: What to Ask, and What Not to Paste