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

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,099
Office Version
2019, 2016, 2013
Platform
Windows
Your =VLOOKUP($A17,'Raw Data'!$A$1:$AJ$3694,2,0) is wrong

change 3694 to a much bigger number atleast 5500
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Dancorkery26

New Member
Joined
Mar 22, 2020
Messages
15
Office Version
2016
Platform
Windows
Arhhh, how did I miss that. Fair play Mole999. A big thank you Fluff and mole999. I was lost on that one
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,512
Office Version
365
Platform
Windows
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

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,512
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

Forum statistics

Threads
1,089,220
Messages
5,406,927
Members
403,113
Latest member
ms_excel_recal_or_die

This Week's Hot Topics

Top