Search code examples
vbaexcelexcel-formulaworksheet-function

Search ZIP code range and pull corresponding Zone

I have a workbook that has the first three digits of a ZIP code in both columns A and B. A is the lower bound of the range and B is the upper bound. I then have the corresponding zone for that ZIP code range in column C. I tried the following formula with no luck. 

=LOOKUP(2,1/((I11>=A10:A101)*(I11<=B10:B101)),D10:D101)

What I am trying to accomplish is to use this formula to grab the appropriate zone for any ZIP code entered, to streamline the process of doing a cost analysis for a potential customer. If it is easier to achieve this using a VBA script I am open to that as well.

Here is an example of the first three rows of data from my workbook:

Lower Bounds | Upper Bounds | Zone
004          | 005          | 008
006          | 007          | 045
009          |              | 045
Solution

  • If Lower Bounds is in A1 and what you are looking up in E2, please try: 

    =INDEX(C:C,MATCH(E2,A:A))

    Make sure ColumnA is sorted ascending.