Bug In excel 2016? - Vlookup occasionally fails to pull data from rows

[Dancorkery26]

Hi,

This is my first post on this forum. I have come across an unusual issue with excel. I am out of ideas!
Would really appreciate help with this.

Problem
I create a leftmost column to use for Vlookup. It contains =LotID&Unique ID formula. This is contained in sheet 1.
Sheet 2: The leftmost column in sheet 2 is ='sheet1'!A:A.
I then perform vlookup to drop detail into specific columns.
99% of the time the data is pulled without a problem.
For some reason occasionally it doesnt pull the information. The detail is there, I can clearly see it.
One thing I have noticed, when I do ctrl+F and try search for it sometimes it does not return.

I have attached a reduced example. Here you can clearly see the issue.

 

[mole999]

would you check LY403 and confirm there is no space after either your point to compare against

 

[Dancorkery26]

would you check LY403 and confirm there is no space after either your point to compare against

Hi Mole999,
I checked for this by clicking into cell and no space visual. The unusual thing on this is sheet 2 column A is literally = formula 'sheet 1'! A:A.
They should be the exact same.

Thank you for quick reply, appreciate the help

 

[mole999]

couple of standard things is =LEN(the cell) on both, =ISTEXT(the cell) will return TRUE, changing 0 at the end of VLOOKUP to 1 would return a near match (for testing)

any difference will cause a fail, hence looking for a space. I know you said sheet 2 has been checked, what about sheet 1

 

[Dancorkery26]

couple of standard things is =LEN(the cell) on both, =ISTEXT(the cell) will return TRUE, changing 0 at the end of VLOOKUP to 1 would return a near match (for testing)

any difference will cause a fail, hence looking for a space. I know you said sheet 2 has been checked, what about sheet 1

Thanks again for help mole999. The len = 5 and the ISTEXT = true. When I change the 0 to 1 it return a ID LTHJ. It should return LY3M.
Its like it doesn't recognise the ID LY3M. i can search for LY3M in sheet 1 and it locates it. If I search for LY3M7 it cant find it. (I confirmed LY3M7 is present.

 

[Dancorkery26]

Thanks again for help mole999. The len = 5 and the ISTEXT = true. When I change the 0 to 1 it return a ID LTHJ. It should return LY3M.
Its like it doesn't recognise the ID LY3M. i can search for LY3M in sheet 1 and it locates it. If I search for LY3M7 it cant find it. (I confirmed LY3M7 is present.

I think it cant find in search because the cell that contains the ID has a formula. Would this be the case?

 

[Fluff]

What cell is the LY3M7 in that cannot be found?

 

[Dancorkery26]

Hi Fluff,

It is in cell A6.

 

[Fluff]

In that case in an unused cell put
=A6="LY3M7"
What does it return?
If false, enter this in another unused cell & drag to the right

+Fluff.xlsm
	A	B	C	D	E	F	G	H
6	LY3M7	76	89	51	77	55	#VALUE!	#VALUE!
Data

Cell Formulas
Range	Formula
B6:H6	B6	=CODE(MID($A6,COLUMN(A1),1))

 

[Dancorkery26]

The reason I think formula is fine is the fact the file vlookups about 5000 rows and 99% of it works no problem. Its like the ID is causing an issue but that is also pulled the same way as all the others