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

#### Jeffrey Green

##### Well-known Member
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

### 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
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
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:
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
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
You're welcome. How about limiting it to something you know won't be "out grown" like A\$2:B\$10000 just a suggestion.

Replies
2
Views
223
Replies
8
Views
779
Replies
5
Views
329
Replies
2
Views
2K