Find out exactly why your lookup won't match

Paste the column you're looking up and the key column you're matching against. For every row that won't match, this tool names the specific reason, an invisible space, a number stored as text, a different letter case, or a typo, and gives you the exact text that would match.

Lookup values (what you're searching for)

One value per line, just like a spreadsheet column.

Key column (the list you're matching against)

This is the range VLOOKUP would search, one value per line.

Lookup value	Result	Why	Text that would match

Write the formula for me

Sometimes the rows are fine and the formula is the problem. Fill in the four boxes and this writes the lookup for you, in both Excel and Google Sheets.

Lookup value cell

The cell holding the value you're searching for, like A2.

Range to search

Starts at the key column and reaches the column you want back, like Sheet2!A:C.

Column to return

Counted from the left edge of the range, so the key column itself is 1.

Match type

Exact Approximate

Exact is what you want in almost every case. Approximate needs the key column sorted.

Separator between arguments

Comma Semicolon

Pick semicolon if your spreadsheet is set to a language that writes decimals with a comma, common across Europe. Comma everywhere else.

Extras Show Not found instead of an error Wrap the lookup value in TRIM

TRIM strips spare spaces off the value you're looking up. If the spare spaces are in the key column instead, clean that column first, because TRIM here can't reach them.

Excel

VLOOKUP

XLOOKUP, for Microsoft 365 and Excel 2021 or newer

Google Sheets

VLOOKUP

Sheets has no XLOOKUP of its own in older files. If yours offers it, the Excel version above works unchanged.

The range is written with dollar signs and the lookup cell is not, on purpose. The dollar signs pin the range in place, so when you drag the formula down the column it keeps searching the same list while the lookup cell moves down with it. Without them, the range slides down too and the rows near the bottom start looking at nothing.

How the check works

Every rule below is checked in order. A row is marked as a clean match only when the two values are character-for-character identical.

Invisible spaces. A leading space, a trailing space, or doubled-up spaces in the middle are trimmed and compared. If that alone explains the mismatch, the space is highlighted in the value so you can see exactly where it is.
Number stored as text. If both values represent the same number once a leading apostrophe, a leading zero, or a thousands comma is accounted for, the tool flags it as a number-versus-text problem, a common cause when product codes or account numbers are typed instead of pasted as real numbers.
Letter case. If the values only differ by uppercase or lowercase letters, that's flagged too. Plain VLOOKUP itself ignores case, but this still breaks case-sensitive tools such as Power Query merges, SQL joins, or code in pandas or VBA.
Near-duplicates. If nothing above explains it, the tool looks for the closest value in your key column by edit distance. A value one or two characters away is flagged as a possible typo, not a certain fix, since it might genuinely be a different value.
No match found. If nothing is close, the value is reported as missing from the key column entirely.