vlookup when value is in a range

[jcollins]

I have the following table (simplified) that indicates what shipping
zone will be assigned to a shipment based upon the first three digits
of the package destination zip code (if going to one of these zip
codes, then this shipping zone applies):

zipcode zone
100-149 135
650-655 132
829-834 136

There are 130 of these ranges in my table.

I have a second list that needs to have zone assigned based upon the
first three digits of the package destination zip code. I'm not sure
how to use vlookup when the value on the source list is contained in a
range on the lookup table. Any help on this is appreciated.

 

[Dave Smith]

One way:

If the table on the left should yeild results like the table on the right

A B C D E
1 zip range zone zipcode zone
2 100-149 135 10101 135
3 650-655 132 12902 135
4 829-834 136 65030 132
83453 136

then put this formula in E2 and copy down

=SUMPRODUCT((LEFT(D2,3)>=LEFT($A$2:$A$4,3)) *
(LEFT(D2,3)<=RIGHT($A$2:$A$4,3)) * $B$2:$B$4)

HTH

-Dave

 

[jaf]

Hi,
Those look suspiciously like UPS zones.
You can download a complete list at
http://www.ups.com/content/us/en/shipping/cost/zones/daily.html
in .csv (Excel) format.
The data should be rearranged to use a vlookup.
Use the ending "first three" in the first column and the zone in the second.
(not 100-149)

So...
3 code zone
100 133
149 135
650 136
655 132

By default Excel's vlookup returns >= your value.
With 65012 in A1 =vlookup(left(a1,3),range,2) will return 136 for a zone.
All you need to deal with is leading zeros in the zipcode.
Enter them with a preceding single quote '01255 and the lookup will work.

 

[Alan Beban]

Assuming your table is in A2:B131, and with the first 3 digits of the
package destination zip code (pdzc) in Cell C1

=VLOOKUP(INDIRECT("C1")&"*",A2:B131,2,0)

will return the shipping zone corresponding to the first occurrence of a
pdzc beginning with the value in Cell C1.

Alan Beban