• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Source Partial Match Lookup

Kristi Waski

New Member
Good morning.

I would like to take my source cell which contains a complete business address, and have it find the zip code within that complete business address and compare it to another reference table. In that reference table are zip codes and the city that is associated. I would like to find the zip code in the source, and end up with the city.
I have found multiple examples of the reverse using wild cards, just nothing that will work for my needs.

Because the source data is not standard (it sometimes included a PO Box or a Room number) I couldn't just trim the data or utilize text to columns on a comma.

123 Main St., Smalltown, MI, 12345, USA

I want to look at the above and have the result be "Smalltown" based on the zip code "12345"

Thank you in advance.
Warmest regards
KW
 
Hi ,

If your text "123 Main St., Smalltown, MI, 12345, USA " is in cell A1 , and your list of zipcodes is in a range named ZipCodes , then the following formula will return the Zip Code :

=SUMPRODUCT(COUNTIF(A1,"*" & ZipCodes & "*") * ZipCodes)

assuming that all your zip codes are numeric.

Narayan
 
Thank you so much Narayan.

I tried above but I am receiving a #VALUE!. I want my result to be "Smalltown" based on the zip code. I attached a sample for you.

Thanks again.
KW
 

Attachments

  • SAMPLE_KW_30OCT2019.xlsx
    142 KB · Views: 2
"ZipCodes" should be replaced with your range that holds list of Zip codes.

However, as Narayank mentioned, Sumproduct alone won't be able to return text as result.

As well, you'd have some issue with your data as Zip really should be 5 char text, rather than numeric for US Zip.
Zip can start with leading zero.
Ex:
Pittsfield should be 01201 etc.

Add helper column with following (in H column).
=TEXT(F2,"00000")

Also, there are few errors in zip
Ex: West Union, SC is not "United" but should be 29696, the other West Union is WV etc.

Then formula becomes...
=LOOKUP(2,1/SEARCH($H$2:$H$960,A2),$G$2:$G$960)

See attached. This should cover most of your address. For those that return error, you'll need to perform manual check.
 

Attachments

  • SAMPLE_KW_30OCT2019.xlsx
    230.5 KB · Views: 6
Thank you both for your assistance. I was aware of the gaps in some of the data and they will require some looking into, but would much rather that than manually manipulating over 10k rows. Its appreciated that I can always count on Chandoo boards for help!
 
Back
Top