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.