ZIP Code Partial Match

  • Hello:


    I have an excel table with many US zip codes and temperature data about those areas. I would like to be able to reference a cell with a ZIP code and do a VLookup on that table to find the temperature data. This I can do.


    The only problem is that the ZIP codes in my table are not inclusive of the entire US. I would like to therefore match the numbers as closely as possible. In other words, I would like to first look for an exact match; if there is no exact match, then look for the first 4 numbers; if that is not exact, then look for the first 3. I don't know which function to use for this.


    A potential problem that I see is this: there might be several entries that match the first 3 or 4 numbers. If this is the case, I would prefer it to choose the closest match (in value).


    I have attached a file that should help clarify my situation. The second worksheet is the table. In the first worksheet, I would like to reference C4 to populate C6:C7 from the second worksheet. Does this make sense?


    I am hopeful for an Excel solution. I have never used VBA. However, I am willing to learn if necessary (it seems that VBA could be a very helpful tool).


    Thanks in advance for your help.


    ktcollardson

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!