Two cells in different workbooks have the exact number, why does the VLOOKUP not find in an array?
- Thread starter Weldon
- Start date
It is important to note: that one of the cells contents was generated by a formula, and in the other workbook the exact same number exists not generated by a formula.
When I key-in the number in the workbook with the cell derived by a formula the VLOOKUP array does find the cell and produces the desired result.
Both cells are formatted as General. . .
When I key-in the number in the workbook with the cell derived by a formula the VLOOKUP array does find the cell and produces the desired result.
Both cells are formatted as General. . .
jeffreyweir
Active Member
It might be an extra space, or a non printing character, or similar.
I blogged about a similar situation at http://dailydoseofexcel.com/archives/2014/08/09/one-of-these-things-is-not-like-the-other/
Furthermore, there's a good explanation of non printing characters at https://support.office.com/en-in/ar...fe5303222c9d?ui=en-US&rs=en-IN&ad=IN&fromAR=1
In regards to non printing characters, in the Unicode character set, there is an additional space character called the nonbreaking space character that has a decimal value of 160. It looks identical to a space character to you and me, but not to Excel. I've snuck a non breaking space into the text in B3 right after the 8:30 bit of text, and an you can see, it looks identical to B2.
But I've also array entered this formula into B5, that returns the string in B2 up to the point that it doesn't match the string in B3:
=LEFT(B2,MATCH(FALSE,MID(B2,1,ROW(INDIRECT("A1:A"&LEN(B2))))=MID(B3,1,ROW(INDIRECT("A1:A"&LEN(B3)))),0))
And these formulas show the character code of the underlying differing character:
=CODE(MID(B2,MATCH(FALSE,MID(B2,1,ROW(INDIRECT("A1:A"&LEN(B2))))=MID(B3,1,ROW(INDIRECT("A1:A"&LEN(B3)))),0),1))
=32
=CODE(MID(B3,MATCH(FALSE,MID(B2,1,ROW(INDIRECT("A1:A"&LEN(B2))))=MID(B3,1,ROW(INDIRECT("A1:A"&LEN(B3)))),0),1))
=160
See the attached file.
Thanks for the great question: I'm going to put a similar example into the book I'm writing.
I blogged about a similar situation at http://dailydoseofexcel.com/archives/2014/08/09/one-of-these-things-is-not-like-the-other/
Furthermore, there's a good explanation of non printing characters at https://support.office.com/en-in/ar...fe5303222c9d?ui=en-US&rs=en-IN&ad=IN&fromAR=1
In regards to non printing characters, in the Unicode character set, there is an additional space character called the nonbreaking space character that has a decimal value of 160. It looks identical to a space character to you and me, but not to Excel. I've snuck a non breaking space into the text in B3 right after the 8:30 bit of text, and an you can see, it looks identical to B2.
But I've also array entered this formula into B5, that returns the string in B2 up to the point that it doesn't match the string in B3:
=LEFT(B2,MATCH(FALSE,MID(B2,1,ROW(INDIRECT("A1:A"&LEN(B2))))=MID(B3,1,ROW(INDIRECT("A1:A"&LEN(B3)))),0))
And these formulas show the character code of the underlying differing character:
=CODE(MID(B2,MATCH(FALSE,MID(B2,1,ROW(INDIRECT("A1:A"&LEN(B2))))=MID(B3,1,ROW(INDIRECT("A1:A"&LEN(B3)))),0),1))
=32
=CODE(MID(B3,MATCH(FALSE,MID(B2,1,ROW(INDIRECT("A1:A"&LEN(B2))))=MID(B3,1,ROW(INDIRECT("A1:A"&LEN(B3)))),0),1))
=160
See the attached file.
Thanks for the great question: I'm going to put a similar example into the book I'm writing.
Chihiro
Excel Ninja
Could it be related to this?
https://support.microsoft.com/en-us/kb/315961
https://support.microsoft.com/en-us/kb/315961
jeffreyweir
Active Member
Hmm. For some reason I can't seem to upload the sample file. That's happened a few times recently. I'll try again later.
neeraj2050
Member
Hi,
The common reason behind it :
a) Extra Spaces (1)Trim will help 2) You may also try Ctrl + h -- Press Space in Find column -- Let the replace column blank -- Press Replace All. So that entire space could be eliminated
b) Data is in text format (You will have to convert that into number)
3) Say your data is written like this
Cell1 : '99
Cell2 L 99
Then you will have eliminate '
Hope it may help you.
The common reason behind it :
a) Extra Spaces (1)Trim will help 2) You may also try Ctrl + h -- Press Space in Find column -- Let the replace column blank -- Press Replace All. So that entire space could be eliminated
b) Data is in text format (You will have to convert that into number)
3) Say your data is written like this
Cell1 : '99
Cell2 L 99
Then you will have eliminate '
Hope it may help you.