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

[Jeffrey Green]

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

 

[jtakw]

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.

 

[Jeffrey Green]

Never mind, i am an Excel moron today . . . he was using relative reference, not absolute
sorry, thanks

 

[Joe4]

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:

[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]

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]

You're welcome. How about limiting it to something you know won't be "out grown" like A$2:B$10000 just a suggestion.