Excel Help - Lookup function?

Scrooge McSam · 09-14-2008, 02:00 PM

Alex,

You did a beautiful job explaining how the VLOOKUP function works. I am in awe. Last time I had to explain this, I did nowhere near as good a job as you just did.

But when I was first reading your overview, something bothered me about it and I couldn't quite put my finger on it. I kept looking at this thread last night trying to figure out what it was. This morning it hit me.

There is just a tiny problem with your method, and it is this part...

Quote:

Then drag that formula down to each order row and it should work fine

Unless you use absolute cell references for your array, Excel will adjust the array reference as you drag it down.

Rather than your formula...

Code:

=VLOOKUP(A2,Address!A1:C1000,3)

... I would suggest...

Code:

=VLOOKUP(A2,Address!$A$1:$C$1000,3)

I would also change your second formula to read...

Code:

=VLOOKUP(A2,Address!$A$1:$D$1000,4)

The absolute cell references will ensure Excel doesn't mangle your array reference as you drag the formula down.

Other than that one small detail, you're dead on.

Good job!

PS - I see I'm not the only one who leaves a little extra room in the array reference. That little trick saves a LOT of time if you regularly edit your array, which I'm constantly doing.

Also, I normally include "FALSE" as the fourth argument (optional) in this function to ensure I get exact matches (I'm normally doing this on numbers). For example...

Code:

=VLOOKUP(A2,Address!$A$1:$C$1000,3,FALSE)

For Betty's purposes though, it's probably not necessary.