Excel Help - Lookup function?

Alex · 09-12-2008, 02:15 PM

Ok, here is doing it from two worksheets in the same workbook. It assumes that in Worksheet 2 (the address one) that each name is unique.

Assumptions for the fomula I'm about to give (so you know how to adjust for your reality

Worksheet 1 has been titled Order
Worksheet 2 has been titled Address

Layout of Order worksheet is:

Code:

  A                  B            C         D
1 Name               Order        City      ZIP
2 Bob Jones          2 couches
3 Caper Realty       4 lamps
4 U of Somewhere     163 desks

Layout of Address worksheet is:

Code:

  A                  B             C         D                 
1 Name               Address       City      ZIP
2

Code:

Bob Jones          123 Some St.  Baltimore 98763
3 Ted Smith          1 Another St. Detroit   98798
4 Caper Realty       456 Some St.  Ogden     97845-6544
5 Boeing Intl.       2 Another St. Chicago   43246-5468
6 U of Somewhere     789 Some St.  Seattle   15498

So, to get column C of the Address worksheet into column C of the Order worksheet put this formula in cell C2:

Code:

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

Then drag that formula down to each order row and it should work fine, resulting in Orders looking like this:

Code:

  A                  B            C         D
1 Name               Order        City      ZIP
2 Bob Jones          2 couches    Baltimore
3 Caper Realty       4 lamps      Ogden
4 U of Somewhere     163 desks    Seattle

Repeat in column D using this formula:

Code:

=VLOOKUP(A2,Address!A1:D1000,4)

and you'll get the ZIP codes as well.

To explain what is happening in that formula:

=VLOOKUP - The VLOOKUP command finds a value in a specified column and returns the value from a specified cell in that row.

(A2, - This is the value you are trying to match. So for an order row, this is the name in the A column of this particular row.

Address! - This tells the formula that you'll be looking in a range on the Address worksheet

A1:C1000, - This is the cell range on the Address! you'll be using. It should cover the entire scope of the information on the Address worksheet. If you'll frequently be adding to this table then the lower-right corner definition (C1000 here) should be much lower than you'll likely ever use.

3) - This is the column that should be used in the row where the matching string is found. So if the match is found in row 163, it will return the value in cell C163. C is the third column.

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.

09-12-2008, 02:15 PM	#1
Alex . Join Date: Feb 2005 Posts: 13,354	Ok, here is doing it from two worksheets in the same workbook. It assumes that in Worksheet 2 (the address one) that each name is unique. Assumptions for the fomula I'm about to give (so you know how to adjust for your reality Worksheet 1 has been titled Order Worksheet 2 has been titled Address Layout of Order worksheet is: Code: A B C D 1 Name Order City ZIP 2 Bob Jones 2 couches 3 Caper Realty 4 lamps 4 U of Somewhere 163 desks Layout of Address worksheet is: Code: A B C D 1 Name Address City ZIP 2 Code: Bob Jones 123 Some St. Baltimore 98763 3 Ted Smith 1 Another St. Detroit 98798 4 Caper Realty 456 Some St. Ogden 97845-6544 5 Boeing Intl. 2 Another St. Chicago 43246-5468 6 U of Somewhere 789 Some St. Seattle 15498 So, to get column C of the Address worksheet into column C of the Order worksheet put this formula in cell C2: Code: =VLOOKUP(A2,Address!A1:C1000,3) Then drag that formula down to each order row and it should work fine, resulting in Orders looking like this: Code: A B C D 1 Name Order City ZIP 2 Bob Jones 2 couches Baltimore 3 Caper Realty 4 lamps Ogden 4 U of Somewhere 163 desks Seattle Repeat in column D using this formula: Code: =VLOOKUP(A2,Address!A1:D1000,4) and you'll get the ZIP codes as well. To explain what is happening in that formula: =VLOOKUP - The VLOOKUP command finds a value in a specified column and returns the value from a specified cell in that row. (A2, - This is the value you are trying to match. So for an order row, this is the name in the A column of this particular row. Address! - This tells the formula that you'll be looking in a range on the Address worksheet A1:C1000, - This is the cell range on the Address! you'll be using. It should cover the entire scope of the information on the Address worksheet. If you'll frequently be adding to this table then the lower-right corner definition (C1000 here) should be much lower than you'll likely ever use. 3) - This is the column that should be used in the row where the matching string is found. So if the match is found in row 163, it will return the value in cell C163. C is the third column.
	Submit to Quotes