Cleaning data so lookups match
These account numbers came from an export and are stored as text: see how they sit on the left. The table has real numbers.
keyboard Typing formulas is easiest on a computer with a keyboard.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Account no. | Name | Account no. | Name | ||
| 2 | 1997 | Elif Brennan | 2109 | |||
| 3 | 1647 | Uma Garcia | 1976 | |||
| 4 | 1381 | Priya Tanaka | 1304 | |||
| 5 | 2298 | Chloe Garcia | 1731 | |||
| 6 | 2858 | Lena Ibrahim | 1997 | |||
| 7 | 1549 | Mateo Novak | ||||
| 8 | 1731 | Victor Costa | ||||
| 9 | 2109 | Oscar Murphy | ||||
| 10 | 1976 | Amara Patel | ||||
| 11 | 1304 | Nadia Novak | ||||
| 12 | ||||||
| 13 | ||||||
| 14 | ||||||
| 15 |
How TRIM and VALUE works
Why does a lookup say #N/A when the value is right there?
Because the two values only look the same. The usual culprits:
- Extra spaces: "
C1042" with a space at the end is not "C1042". - Numbers stored as text: "1042" from an export is not the number 1042.
- Different punctuation: "AB-12" is not "
AB12".
- Extra spaces: "
How do you fix it inside the lookup?
Clean the value you look up with, so the original data stays untouched:
=XLOOKUP(TRIM(D2),A2:A11,B2:B11)removes spaces at the start and end.=XLOOKUP(VALUE(D2),A2:A11,B2:B11)turns "1042" into 1042.=XLOOKUP(SUBSTITUTE(D2,"-",""),A2:A11,B2:B11)removes the dashes.
They can be combined:
VALUE(TRIM(D2)).What if the table is the messy side?
Clean the lookup range instead:
=XLOOKUP(D2,TRIM($A$2:$A$11),$B$2:$B$11)works in Excel 365, which cleans every cell as it searches. In older Excel, add a cleaned helper column to the table.How do you spot the problem?
Compare the two cells with
=D2=A5(FALSEmeans they differ), check the lengths withLEN, and look for text sitting on the left of the cell. The Lookup & Merge tool on this site explains every miss for you.
Know someone fighting with a spreadsheet? Send them this page.