Matching Zip Codes to City

[00264657]

Hello,

In one sheet titled "Crime rates in cities", I have cities and state names in a column such as:

Adamsville, AL

Alexander City, AL 

Aliceville, AL

In the third sheet titled "Zip Codes for cities", I have the cities and state with the zip code in seperate columns such as: 

Adamsville, AL           35005

Adger, AL                  35006 

Alabaster, AL             35007

I want to the zip codes to be matched to the city in the first sheet. Also, cities can have more than one zip code and I would like all those zip code to be included in one cell.  Using VLOOKUP only returns "N/A" View spreadsheet for more information.

Thanks

[#2]

Hello,

VLOOKUP can not work, as the lookup values do not match the values from the Zip codes table. So, if preserving the current layout and formulas of your sheets, you can use a mapping table which maps the state names to their abreviations, e.g. ALABAMA to AL. In columns AD and AE I added a mapping table, you should extent and which looks like this:

State	Code
ALABAMA	AL
ALASKA	AK
ARIZONA	AZ

Then in column Z of your first sheet I added a helper column with the formula:

=IFERROR(INDEX($AE$5:$AE$7,MATCH(UPPER(TRIM(RIGHT(U5,LEN(U5)-FIND(",",U5)))),$AD$5:$AD$7,0),1),"")

This gives you e.g. the abreviated state name AL for ALABAMA from the mapping table and applied to your first data line in the first sheet. You can then autofill down the formula. In column AA, I checked the length of the result from Z and if its ok, I prepended the city name. Now you have the full name looking like the value in the zip codes sheet. In column AB I extracted the zip code by an INDEX/MATCH combination.

Note that I did not extensively test and only filled some rows. You have e.g. entries in you zip codes table which have (PO Boxes) at the tail, these entries are not considered. I attached your file with my modifications here.

Best,

Mourad

[#3 · Accepted solution]

Hi to all!

In this moment, you can't find the zip's code, because you have the whole name of state, and not the abbreviation of that state.

If you have a helper table with the abbreviation's of the states and aditional a helper column in Sheet "Zip Codes for cities", you can get all Zip Codes for the states.

Check file with an option.  Blessings!

[#4]

Thank you!