Curious Phenomenon...Vlookup not matching


Eric @ BP-EVV

I have a vlookup that is retunring an "#N/A" for one item in a spreadsheet.
I have added a "trim" function to the mix to get rid of extra spaces and it
still returns the same. The values in both the cell being looked up and the
cell that SHOULD contain the match appear to be the same text to me. Is
there a way to determine exactly what is the differences ? Both cells contain
text only (or at least that's all that is visible to the naked eye !)

Thanks !



Duke Carey

One simple test is to enter a formula that checks to see if the two are


which will return TRUE if they match and FALSE if they don't. You'll almost
assuredly get a FALSE because otherwise the VLOOKUP would match the two.

In addition to TRIM you could try CLEAN() to remove non-printing characters.


Although TRIM gets rid of unwanted normal spaces, it does not get rid
of the non-breaking space character with a code of 160 - you can get
rid of these with Find/Replace. As you only have one offending item,
though, it will be easier to <copy> the lookup value and <paste> it
into the appropriate cell of the table.

If you want to investigate further, you can do =LEN(cell) on both
cells to find out how many characters there are in each. Also, you can
select one of the cells and then click in the far right of the formula
bar as if to edit it - if you have spaces at the end of the values you
can see this from where the cursor ends up.

Hope this helps.


Eric @ BP-EVV

It did fail your test, and the CLEAN function did the trick too....I was
unaware of that function. Thanks...I can go home and feel good about the day
today....I learned something new !


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads