If VLOOKUP says #N/A but the merchant name is plainly there, the two cells almost always differ by an invisible character. Usually it is a trailing space or a non-breaking space that bank exports and web copies bring in. Compare =LEN(B2) with the length you expect, then clean the text with =TRIM(CLEAN(SUBSTITUTE(B2,CHAR(160)," "))).
How do I prove there is a hidden character?
Do not trust your eyes. Put both values side by side and test them with formulas. Say B2 is the narration cell and F2 is the name in your merchant table, both reading Swiggy.
- =LEN(B2) returns 7 while =LEN(F2) returns 6: one invisible character is hiding in B2
- =B2=F2 returns FALSE, even though the words look the same
- =CODE(RIGHT(B2,1)) returns 32 for an ordinary space or 160 for a non-breaking space; in Sheets, =UNICODE(RIGHT(B2,1)) does the same job
- =EXACT(B2,F2) is a stricter version of the equals test and also separates capital letters
The check on the table side matters too. Many people clean the transactions and forget that the lookup column on the table also carries spaces, typed in by hand years ago.
What do TRIM and CLEAN fix, and what do they miss?
| Function | Removes | Leaves behind |
|---|---|---|
| TRIM | Leading and trailing ordinary spaces, and repeated spaces inside the text | Non-breaking spaces (code 160) |
| CLEAN | Line breaks, tabs and other unprintable characters (codes 0 to 31) | Code 160, which is printable |
| SUBSTITUTE | Any exact character you name, including code 160 | Nothing you did not name |
The non-breaking space is the usual culprit when TRIM seems to do nothing. It looks like a space but has a different code, so TRIM ignores it. Swap it for a normal space first, then let TRIM finish the job.
What is the one formula that cleans everything?
Put this in a helper column, say E2, and copy it down:
=TRIM(CLEAN(SUBSTITUTE(B2,CHAR(160)," ")))
Read it from the inside: SUBSTITUTE turns code 160 into a plain space, CLEAN strips unprintable characters, TRIM tidies the spaces. Run =LEN(E2) again; the 7 should now be 6. If your Excel returns a strange symbol for CHAR(160), use UNICHAR(160) instead, which exists in Excel 2013 and later and in Google Sheets.
Then point the lookup at the clean column: =VLOOKUP(E2,$F$2:$G$200,2,FALSE).
Should I keep the helper column forever?
You have two choices. Keeping it is safest, because every month's paste runs through the same cleaning. Alternatively, copy the clean column and use Paste special, Values only, over the original narration, then delete the helper. That is neater, but you must repeat it for every new import.
A worked mini-case
Say a statement export has "Swiggy" followed by a non-breaking space in B2, and your table has "Swiggy" in F2 with category "Food delivery" in G2. =VLOOKUP(B2,$F$2:$G$2,2,FALSE) returns #N/A. TRIM(B2) alone still gives length 7. After SUBSTITUTE with CHAR(160), the length is 6, and the lookup returns Food delivery. One character was the whole problem.
Habits that stop it coming back
- Clean narrations as the first step after every paste, before any lookup
- Never type merchant names into the table by hand if you can paste them from a cleaned column
- Wrap the lookup in IFNA so a miss shows Uncategorised, then filter those rows
- Keep one sheet for raw data and another for cleaned data, so you can always go back
If the cleaning keeps eating your evenings, importing the statement into SpendixAI and checking the rows before saving skips this step, though you will still want to scan the merchant names it shows you.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


