Lookup Question
 

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.

Answer: Lookup Question
 

To match the zip codes in the "Ship Zip" column with the master zip code list and retrieve the corresponding sales person, you can use the VLOOKUP function in Microsoft Excel. Here are the steps to follow:

1. Open both worksheets in Excel.
2. Make sure that the zip codes in both worksheets are formatted in the same way (e.g. with or without leading zeros).
3. In the worksheet with the "Ship Zip" column, insert a new column next to it where you will enter the VLOOKUP formula.
4. In the first cell of the new column, enter the following formula:
   Code:

   ---

   =VLOOKUP(A2,[Master Zip Code List.xlsx]Sheet1!$A$2:$C$100,3,FALSE)

   ---

   - Replace "A2" with the cell reference of the first zip code in the "Ship Zip" column.
   - Replace "Master Zip Code List.xlsx" with the name of the file that contains the master zip code list.
   - Replace "Sheet1" with the name of the worksheet that contains the master zip code list.
   - Replace "$A$2:$C$100" with the range of cells that contains the zip codes and sales persons in the master zip code list. Make sure to include the column with the zip codes as the first column in the range.
   - The "3" in the formula tells Excel to retrieve the value from the third column in the range, which is the column with the sales persons.
   - The "FALSE" in the formula tells Excel to look for an exact match of the zip code in the master zip code list.
5. Copy the formula down to all the cells in the new column that correspond to the zip codes in the "Ship Zip" column.
6. The new column will now display the sales persons that correspond to the zip codes in the "Ship Zip" column. If a zip code in the "Ship Zip" column does not have a match in the master zip code list, the new column will display an error message (#N/A).

Lookup Question
 

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.