Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2013
    Posts
    8

    Unanswered: Match zip code to city

    I have a very large database that i am frequently adding to and it involves entering addresses of buildings. My problem is i get tired of going through the database to find the zip code that matches that city should it not be given to me. I would like to enter the city and it search down the city column and inserts a matching zip code in the correct column. At this point i have almost every city and its zip code in the database, so i don't need to worry about creating a whole different database of many city and zips to reference.

    My Layout
    Column M: Lists the City
    Column O: Where i want the zip code to be inserted

    Thanks in advance to anybody who is able to help me out.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Instructions using Excel 2010
    • Highlight your entire table and on the "Home" tab on the Ribbon click "Format as Table".
    • Click on any cell in your table and note the new "Table Tools > Design" tab on the Ribbon. Click on this
    • On the left hand side of this tab there is a box to give your table a name. In this example I called it "Addresses"
    • Add a new column to your table called "Calculated Zipcode"
    • Add the following formula to any cell in this new column
      Code:
      =INDEX(Addresses, MATCH([@City], [City], 0), COLUMN([Zipcode]))


    I have attached a file to this post called "zipcode.txt".
    This is actually a spreadsheet but I've had to change the extension to upload it.
    Change the file extension to *.xlsx and open it up to see this example in action.
    Attached Files Attached Files
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2013
    Posts
    8
    Quote Originally Posted by gvee View Post
    Instructions using Excel 2010
    • Highlight your entire table and on the "Home" tab on the Ribbon click "Format as Table".
    • Click on any cell in your table and note the new "Table Tools > Design" tab on the Ribbon. Click on this
    • On the left hand side of this tab there is a box to give your table a name. In this example I called it "Addresses"
    • Add a new column to your table called "Calculated Zipcode"
    • Add the following formula to any cell in this new column
      Code:
      =INDEX(Addresses, MATCH([@City], [City], 0), COLUMN([Zipcode]))


    I have attached a file to this post called "zipcode.txt".
    This is actually a spreadsheet but I've had to change the extension to upload it.
    Change the file extension to *.xlsx and open it up to see this example in action.
    Just now had the time to check back to this thread. Tried out your suggestions and not having much luck. And i did change the formula to match my spreadsheet's column names. It just shows up with a zero in the calculated zipcodes column.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Try get this formula to return something first then
    Code:
    =MATCH([@City], [City], 0)
    George
    Home | Blog

  5. #5
    Join Date
    Jul 2013
    Posts
    8
    Quote Originally Posted by gvee View Post
    Try get this formula to return something first then
    Code:
    =MATCH([@City], [City], 0)

    That oddly returns 00055. No idea where that's even coming from.

    Tried a second time this time putting a column at the very end of the table instead of right after the City Column and now it displays the value of 1
    Last edited by Flyers28; 07-23-13 at 09:02.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you upload a sample of your spreadsheet (with sensitive data removed!)? (change the file extension to *.txt to upload)
    George
    Home | Blog

  7. #7
    Join Date
    Jul 2013
    Posts
    8
    Quote Originally Posted by gvee View Post
    Can you upload a sample of your spreadsheet (with sensitive data removed!)? (change the file extension to *.txt to upload)
    Uploaded sample of my spreadsheet. I think what its doing is instead of returning the name of the city with MATCH([@CITY], [CITY], 0) its returning the number of rows down it is from the top.
    Attached Files Attached Files
    Last edited by Flyers28; 07-24-13 at 09:28.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That's exactly what Match() should be doing.
    In your spreadsheet, on the last column (you've titled it "test") I added this formula
    Code:
    =INDEX([ZIP CODE], MATCH([@CITY], [CITY], 0),  0)
    Now go to the bottom of the table and add a new entry e.g "North Wales".
    The "test" column will have a value of "19454"
    George
    Home | Blog

  9. #9
    Join Date
    Jul 2013
    Posts
    8
    Quote Originally Posted by gvee View Post
    That's exactly what Match() should be doing.
    In your spreadsheet, on the last column (you've titled it "test") I added this formula
    Code:
    =INDEX([ZIP CODE], MATCH([@CITY], [CITY], 0),  0)
    Now go to the bottom of the table and add a new entry e.g "North Wales".
    The "test" column will have a value of "19454"
    Awesome that works, thanks. Now whats the best way to get "Zip Code" Column to = "test" column automatically when i enter a new entry to the table. I don't want to overwrite the entire column with =[@TEST] cause it messes up some of the entries that have different zip codes for the same city. (Those zips are a small part of the database so i don't mind manually finding and entering those zips by hand should a future entry have a different zip). I just want all future entries to reference the new "Test" column formula.
    Last edited by Flyers28; 07-24-13 at 12:34.

Posting Permissions

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