Filling Zip by City

C

Chip Pearson

First of all, you would need some table of the various cities and
their zip codes (and this assumes that there is but one zip code per
city). Suppose that data table is in K1:L100, with city names in
column K and zip codes in column L. Then, you could use the VLOOKUP
function to loop up the city in column K and get the corresponding zip
code from column L. If your city names that need zip codes assigned to
them are in A1:A100, enter

=VLOOKUP(A1,K$1:L$100,2,FALSE)

in cell B1 and fill down to B100.

You should probably provide more detail about what you have and what
you need to do.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
P

Perry

I have a customer spreadsheet and I would like the Zip column to
automatically fill when I enter a City in the City Column. I tried writing a
module to do this but it does not work. I used a series of IF then
Statements to define Cities and zip codes. And I defined the variables as
strings.

If City="CityName" then Zip = "zipcode"

I did not use any cities that had multiple zip codes. That comes Later :)
Thnx Perry
 
R

Rick Rothstein

You will need to use the worksheet's Change event... check that the
Target.Column is the same as the column where your **cities** are typed in
and, if it is, then look up the zip code and put it into the appropriate
column where your zip codes go (on the same row as the Target).
 
C

Chip Pearson

The VLOOKUP approach is still feasible. If you Cities are in column A
and you have the city and zip code pair in columns K:L on Sheet2,
enter the following formula in A1 and fill down as necessary:

=IF(A1="","",VLOOKUP(A1,Sheet2!K1:L1000,2,FALSE))

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
Top