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

Dancorkery26

New Member
Joined
Mar 22, 2020
Messages
15
Office Version
2016
Platform
Windows
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.
 

Attachments

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,099
Office Version
2019, 2016, 2013
Platform
Windows
would you check LY403 and confirm there is no space after either your point to compare against
 

Dancorkery26

New Member
Joined
Mar 22, 2020
Messages
15
Office Version
2016
Platform
Windows
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

Moderator
Joined
Oct 23, 2004
Messages
10,099
Office Version
2019, 2016, 2013
Platform
Windows
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

New Member
Joined
Mar 22, 2020
Messages
15
Office Version
2016
Platform
Windows
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

New Member
Joined
Mar 22, 2020
Messages
15
Office Version
2016
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,512
Office Version
365
Platform
Windows
What cell is the LY3M7 in that cannot be found?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,512
Office Version
365
Platform
Windows
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
ABCDEFGH
6LY3M77689517755#VALUE!#VALUE!
Data
Cell Formulas
RangeFormula
B6:H6B6=CODE(MID($A6,COLUMN(A1),1))
 

Dancorkery26

New Member
Joined
Mar 22, 2020
Messages
15
Office Version
2016
Platform
Windows
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
 

Forum statistics

Threads
1,089,217
Messages
5,406,916
Members
403,111
Latest member
Donbozone

This Week's Hot Topics

Top