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.