+ Reply to Thread
Results 1 to 9 of 9

Lookup for the Zip Code of a Given Address

  1. #1
    Forum Contributor
    Join Date
    11-25-2018
    Location
    Philippines
    MS-Off Ver
    Ms Office 365
    Posts
    217

    Lookup for the Zip Code of a Given Address

    Hi, I have a problem to generate a ZIP code from a given address. I tried Index+match and If+iserror+search but I cannot because the address is too long and the reference is shorter.

    I have attached a sample file on it.

    thanks in advanced.
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Lookup for the Zip Code of a Given Address

    Try this...
    =LOOKUP(1,-SEARCH($A$2:$A$27,F6),$C$2:$C$27)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Banned User!
    Join Date
    02-26-2020
    Location
    US
    MS-Off Ver
    2010
    Posts
    157

    Re: Lookup for the Zip Code of a Given Address

    Hello,

    I think you meant to lookup by Zip Code so I used index match formula to get the Municipal/City and Province.

    This formula then drag over:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Lookup for the Zip Code of a Given Address

    And that is the way your selected location will be formatted? Not knowing your area, can there be the same named Municipality/City in more than one province? Because this would require matching on both locations.
    Will addresses be that way where the municipality is ALWAYS after the second comma and before the province?
    Is there any way you can adjust the address to simply have the Municipality/City alone - this would obviously simplify the formula greatly.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Lookup for the Zip Code of a Given Address

    Quote Originally Posted by RChad View Post
    Hello,

    I think you meant to lookup by Zip Code so I used index match formula to get the Municipal/City and Province.
    Nope, this is a question that pops up on the forum from time to time - OP has a full street address and needs to find it's zip code from a list of cities

    ...I have a street addy, whats the zip code...

  6. #6
    Banned User!
    Join Date
    02-26-2020
    Location
    US
    MS-Off Ver
    2010
    Posts
    157

    Re: Lookup for the Zip Code of a Given Address

    My apologies I attached the ability to lookup by Zip Code or Municipal/City.


    Please see attached

  7. #7
    Forum Contributor
    Join Date
    11-25-2018
    Location
    Philippines
    MS-Off Ver
    Ms Office 365
    Posts
    217

    Re: Lookup for the Zip Code of a Given Address

    Quote Originally Posted by FDibbins View Post
    Try this...
    =LOOKUP(1,-SEARCH($A$2:$A$27,F6),$C$2:$C$27)
    This works for now, Thanks.
    Will be using this for now and see it it works with identical Street/barangay name and a municipality.

    Thank you so much this saves me a lot.
    Last edited by archvanarl; 03-13-2020 at 10:21 AM.

  8. #8
    Banned User!
    Join Date
    02-26-2020
    Location
    US
    MS-Off Ver
    2010
    Posts
    157

    Re: Lookup for the Zip Code of a Given Address

    Check the attached revision in #6 it should work for you.

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Lookup for the Zip Code of a Given Address

    Happy to help and thanks for the feedback

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Lookup IP address in range
    By evolv12 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-03-2014, 02:23 AM
  2. [SOLVED] Lookup help - can lookup address values that fall within a range?
    By Ruthie83 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-16-2013, 10:25 PM
  3. Lookup address and not values
    By marshahannah in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-24-2013, 09:17 AM
  4. [SOLVED] Lookup Hyperlink and go to Address with Macro
    By cfherd in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-11-2012, 04:24 AM
  5. Replies: 1
    Last Post: 03-08-2012, 01:57 PM
  6. lookup a cell address with criteria value
    By abrunstad in forum Excel General
    Replies: 7
    Last Post: 03-27-2009, 11:17 AM
  7. [SOLVED] lookup klient address
    By reon in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-03-2005, 03:50 PM
  8. [SOLVED] LOOKUP & RETURN CELL ADDRESS
    By Carolan in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-05-2005, 11:05 PM

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