Equation to Take a Zip code and Convert it to a City and State

[jkfoxworth]

Hi,

I have tried to do this a few different ways with out success. I have a list of zip codes and I want to convert it to a City and State. I have a master list of Zip Codes and their corresponding City and States. zip_code_image.JPG


I have attached an image above ^ that hopefully shows this. I assume I would have to use VLOOKUP or INDEX(MATCH)?

Thanks for any help and hopefully the image gives enough information.

[Glenn Kennedy]

No one is going to want to retype your data for you (to test a solution). Please attach an Excel sheet, not a picture of one.

[ImokatExcel]

A Vlookup function will work, make sure to convert your Zip code columns to the same format( I see the green triangle in the top left corner of the cells you might have to fix that).

[jason.b75]

I see the green triangle in the top left corner of the cells you might have to fix that.

That green triangle is warning the user of numbers stored as text (to prevent leading zeros being dropped).

Given that all of the relevant data is in the same format, there would be nothing to fix.

[jkfoxworth]

I just added the excel attachment, I tried previously but it exceeded the size requirement so I reduced it a bit, thanks again for any help!

[AlKey]

Enter formula in C2 and pull it to the right and then down.

Please Login or Register  to view this content.

[jkfoxworth]

Thanks AlKey, and everyone else, this gives me what I need, the data is a bit off like for state it's giving me the zipcode again and country it's giving me the state, but I can modify that. Thank you so much, this forum is awesome and will be adding reputations.

[Glenn Kennedy]

State:
=IFERROR(VLOOKUP($B2,$G:$I,3,0),"Not Found")

City;
=IFERROR(VLOOKUP($B2,$G:$I,2,0),"Not Found")

Don't use whole column ranges for the LOOKUP, as Excel will waste ages looking in empty cells.

[AlKey]

Thanks AlKey, and everyone else, this gives me what I need, the data is a bit off like for state it's giving me the zipcode again and country it's giving me the state, but I can modify that. Thank you so much, this forum is awesome and will be adding reputations.

Actually it does provide correct results.

Please see attached file

[jason.b75]

As the data is already formatted as a table, then you could simply use

=INDEX(Table2[State Abbreviation],MATCH(B2,Table2[ZIP Code],0))

and

=INDEX(Table2[City],MATCH(B2,Table2[ZIP Code],0))

[DTREE]

This is awesome, thanks so much.