A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Lookup Question



 
 
Thread Tools Display Modes
  #1  
Old April 17th, 2010, 09:51 PM posted to microsoft.public.excel.worksheet.functions
mj
external usenet poster
 
Posts: 258
Default 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.




  #2  
Old April 18th, 2010, 12:26 AM posted to microsoft.public.excel.worksheet.functions
JLatham
external usenet poster
 
Posts: 1,896
Default 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.




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:44 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.