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]

Your =VLOOKUP($A17,'Raw Data'!$A$1:$AJ$3694,2,0) is wrong

change 3694 to a much bigger number atleast 5500

 

[Dancorkery26]

Arhhh, how did I miss that. Fair play Mole999. A big thank you Fluff and mole999. I was lost on that one

 

[Fluff]

Ok, thanks for that.
The problem is because the vlookup is only looking at A1:AJ3694, but the data goes down to row 5106. So some of the values you are trying to lookup are below row 3694.
Values like LY3M4 work because it exists more than once on the raw data sheet, namely rows 2640 & 4679
If you change the vlookup range A1:AJ5106, it should get rid of the problem, but may not pickup the correct value if it appears more than once on the data sheet.

Hadn't noticed the thread had gone to page#3.

 

[Dancorkery26]

Ok, thanks for that.
The problem is because the vlookup is only looking at A1:AJ3694, but the data goes down to row 5106. So some of the values you are trying to lookup are below row 3694.
Values like LY3M4 work because it exists more than once on the raw data sheet, namely rows 2640 & 4679
If you change the vlookup range A1:AJ5106, it should get rid of the problem, but may not pickup the correct value if it appears more than once on the data sheet.

Hadn't noticed the thread had gone to page#3.

Me either, thanks again Fluff. I am surprise how generous ye are with your time. Take care and stay safe

 

[Fluff]

Glad we could help & thanks for the feedback