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
identical

=(a1=d1)

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.

 

[Pete_UK]

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.

Pete

 

[Dave Peterson]

Chip Pearson has a very nice addin that will help determine what those cells
really contain:
http://www.cpearson.com/excel/CellView.aspx

 

[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 !

 

[Pete_UK]

Chip's site seems to be unavailable at the moment.

Pete

 

[Dave Peterson]

It's working as of this post.

Chip's site seems to be unavailable at the moment.

Pete