I fixed it, but boss wants to know WHY his way didnt work . . . Simple VLOOKUP

Jeffrey Green

Well-known Member
Joined
Oct 24, 2007
Messages
1,017
He used =Vlookup(A2,Sheet2!A2:B50,2,0) Works on some rows, #NA on others (the item being looked for is always there)

I used =Vlookup(A2,Sheet2!A:B,2,0) it works
(I have told him 100 times to name his ranges, but he is stubborn)

Just curious why his formula won't work on all rows . . .

thanks
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

To be honest, if the data table is in the range A2:B50, in my opinion, his formula is better, cause yours is checking the Entire Columns of A and B (over a million rows).
Lets say you're checking A2, second row, and dragging the formula down, checking A3, A4, A5, etc.

Your boss's formula should be =VLOOKUP(A2,Sheet2!A$2:B$50,2,0) and this is faster than your version.
 
Last edited:

Jeffrey Green

Well-known Member
Joined
Oct 24, 2007
Messages
1,017
Never mind, i am an Excel moron today . . . he was using relative reference, not absolute
sorry, thanks
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,518
Office Version
  1. 365
Platform
  1. Windows
If your boss didn't lock the range down, like jtakw showed in his formula, as you copy it down the range will move, i.e. next line will be:
Code:
[COLOR=#333333]=Vlookup(A3,Sheet2![/COLOR][COLOR=#ff0000][B]A3:B51[/B][/COLOR][COLOR=#333333],2,0)[/COLOR]
As you keep copying the formula down, you keep cutting more and more out the original range.
So, you need to lock it down, either by using the absolute range references like jtakw showed you, or by naming your range.
 

Jeffrey Green

Well-known Member
Joined
Oct 24, 2007
Messages
1,017
Jtakw - - - thanks . . . i prefer to use whole columns . . . the speed hit is negligible in our world. Also, that dept is know for adding additional data to the Array . . . . . without knowing they have to adjust the array.
thanks though!!
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
You're welcome. How about limiting it to something you know won't be "out grown" like A$2:B$10000 just a suggestion.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,532
Messages
5,529,393
Members
409,870
Latest member
Well59
Top