Lounge of Tomorrow - View Single Post - Excel Help

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.

09-12-2008, 02:15 PM	#5
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