Curious Phenomenon...Vlookup not matching

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 !

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.

"Eric @ BP-EVV" wrote:

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 !

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

On Aug 27, 12:57*pm, Eric @ BP-EVV
wrote:
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 !

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 wrote:

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 !

--

Dave Peterson

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 !

"Duke Carey" wrote:

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.

"Eric @ BP-EVV" wrote:

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 !

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

Pete

On Aug 27, 1:18*pm, Dave Peterson wrote:
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 wrote:

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 !

--

Dave Peterson

It's working as of this post.

Pete_UK wrote:

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

Pete

On Aug 27, 1:18 pm, Dave Peterson wrote:
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 wrote:

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 !

--

Dave Peterson

--

Dave Peterson