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.

 

[Dancorkery26]

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))

Another call with same issue. A16="LY403" RETURNS true

 

[Dancorkery26]

Another call with same issue. A16="LY403" RETURNS true

Your formula also return 76

 

[Fluff]

The formula needs to be dragged to the right, as shown in post#9, however if the other formula returns true there is no need.

Also try =A6="LY3M7"
but change the A6 to the cell on the other sheet

 

[Dancorkery26]

So the plot thickens. I created a new sheet. In cell A2 I vlookup'd LY403 from sheet 1 then performed the same vlookups as original (causing the problem). It worked fine. Did the same with sheet 2 and it worked as well. Very strange!

 

[Fluff]

Can you supply the workbook?

 

[Dancorkery26]

The formula needs to be dragged to the right, as shown in post#9, however if the other formula returns true there is no need.

Also try =A6="LY3M7"
but change the A6 to the cell on the other sheet

Also true Fluff!

 

[Dancorkery26]

Can you supply the workbook?

Am I permitted to share through this forum?

 

[Fluff]

You cannot upload to the site, but you could upload to OneDrive, GoogleDrive or similar. Mark for sharing & post the link to the thread

 

[Dancorkery26]

You cannot upload to the site, but you could upload to OneDrive, GoogleDrive or similar. Mark for sharing & post the link to the thread

Sample.xlsx

drive.google.com

 

[Dancorkery26]

Sample.xlsx

drive.google.com

Thanks again for all the help with this