Prev: What happened to setting up a constant using F4?
Next: Sorting
From: MJ on 17 Apr 2010 16:51
I have a worksheet that has Zip codes that needs to be matched with a master
zip code list with assigned names. One worksheet has a column called “Ship
Zip” the other worksheet has two columns that I need to relate to the “Ship
Zip” column, the first is a column “Zip Code” and the second is a column
“Sales Person” the hope is to set a formula in the worksheet with the “Ship
Zip” that would pull both columns from the other worksheet, if not I would
need to pull the column “Sales Person” that provides the exact match of the
zip codes in both worksheets.




From: JLatham on 17 Apr 2010 19:26
Without something else to match on, I don't see you automatically pulling the
zip code from the master list over to the "Ship Zip" entry on the second
sheet, but once you do have a zip code in that column, you should be able to
use either VLOOKUP() or LOOKUP() to get the sales person's name.

If, on the master list sheet, the zip code is to the LEFT of the sales
person name, then you can use VLOOKUP(). Let's assume that Ship Zip is
column G on the second sheet, and on the master list you have ShipZip in
column R with sales person's names in T: For row 2 on the second sheet:
=VLOOKUP(G2,'Master Sheet'!$R$2:$T$1000,3,FALSE)
the ,3, is there because the sales person's name is in the 3rd column of the
lookup table( R, S, T)

But, if the master sheet is set up with the sales person's names in a column
to the left of the zip codes column, you'll need to use LOOKUP() AND the zip
codes will have to be sorted in ascending order (least to largest). Let's
say names are in R and zip codes in T this time (still working in G on the
other sheet):

=LOOKUP(G2,'Master Sheet'!$T$2:$T$1000,'Master Sheet'!$R$2:$R$1000)

"MJ" wrote:

> I have a worksheet that has Zip codes that needs to be matched with a master
> zip code list with assigned names. One worksheet has a column called “Ship
> Zip” the other worksheet has two columns that I need to relate to the “Ship
> Zip” column, the first is a column “Zip Code” and the second is a column
> “Sales Person” the hope is to set a formula in the worksheet with the “Ship
> Zip” that would pull both columns from the other worksheet, if not I would
> need to pull the column “Sales Person” that provides the exact match of the
> zip codes in both worksheets.
>
>
>
>
 | 
Pages: 1
Prev: What happened to setting up a constant using F4?
Next: Sorting