+ Reply to Thread
Results 1 to 7 of 7

Zip code data that populates the city and county.

  1. #1
    Registered User
    Join Date
    10-31-2012
    Location
    Tampa, Florida
    MS-Off Ver
    Excel 2010
    Posts
    12

    Zip code data that populates the city and county.

    I want to be able to have a user enter a ZIP CODE into cell G4 on a sheet titled DEMOGRAPHICS that will populate cell F4 with the correct CITY and H4 with the correct COUNTY. I figure I may have to enter the correct city and county for each zip code on a sheet I've entitled DATA and then have the formula pull the data from there. I was going to enter all the data from this URL http://www.zipcodestogo.com/Florida/ on the sheet I've entitled DATA but I want to know if this type of formula is even possible first.

    Thank you for all input!

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2404 Win 11 Home 64 Bit
    Posts
    23,855

    Re: Zip code data that populates the city and county.

    You will want to use the vlookup function in both destination cells.

    Here is a tutorial on vlookup.

    http://www.techonthenet.com/excel/formulas/vlookup.php

    Alan
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Zip code data that populates the city and county.

    Once you have the data listed in a Sheet

    You could use a Vlookup
    www.contextures.com/xlFunctions02.html

    or a combination of Index and MATCH functions
    www.contextures.com/xlFunctions03.html
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  4. #4
    Registered User
    Join Date
    10-31-2012
    Location
    Tampa, Florida
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Zip code data that populates the city and county.

    Thank you so much! One more question to complete this. I have this formula in F4 =VLOOKUP($G4,Data!B5:D1476,2) and this formula in H4 =VLOOKUP($G4,Data!B5:D1476,3) returning the correct values for the data I enter in G4. How do I modify these formulas to return a blank cell instead of #N/A in cloumns F and H when there is no value entered in colum G? Thank you!

  5. #5
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Zip code data that populates the city and county.

    I would change these to
    F4
    =IFERROR(VLOOKUP($G4,Data!$B$5:$D$1476,2,FALSE),"")

    H4
    =IFERROR(VLOOKUP($G4,Data!$B$5:$D$1476,3,FALSE),"")

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2404 Win 11 Home 64 Bit
    Posts
    23,855

    Re: Zip code data that populates the city and county.

    IfError(VLOOKUP($G4,Data!B5:D1476,3),"")

    See if that works for you

  7. #7
    Registered User
    Join Date
    10-31-2012
    Location
    Tampa, Florida
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Zip code data that populates the city and county.

    I used this: =IF($G4="","",VLOOKUP($G4,Data!B5:D1476,2)) THANK YOU BOTH AGAIN SO MUCH!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1