Lookup Value From Range Within Another Cell. Help!

[gregz1234]

Hello,

I'm attempting to pull in zones (value in collumn B) based off of the first three digits of a zip code.

The first three digits of the zip code will determine the zone.

For example, a zipcode of 00736, will return a zone of 45 (based on the table below).

A	B
Dest Zip	Zone
004-005 <colgroup><col><col></colgroup><tbody> </tbody>	007
006-007	045
009	045

<tbody>
</tbody>

Any tips for helping to create a formula for this? I have used a LOOKUP formula, however I have noticed sporadic errors in my data set as a result.

thank you all in advance,
Greg

 

[Jeffrey Mahoney]

You can use vLookup, but you'll need to clean your zip code values. 004-005 isn't easy for the vlookup to find your values. With 004 and 005 in separate rows it will work. Vlookup can be used to find values within the range of 004 to 005 by its default value of TRUE for [range_lookup]

 

[gregz1234]

I will give that a try. thanks!

Will this work with larger ranges? ie: 724-803 as opposed to 004-005?

 

[Jeffrey Mahoney]

It will. Once the value is higher then the lookup value, it returns the last row.

 

[gregz1234]

It will. Once the value is higher then the lookup value, it returns the last row.

Thanks John. Just to confirm, if for example the first part of the range is in one collumn and the second part of the range in the other collum, where would the array start?

 

[Jeffrey Mahoney]

In this example Table:

A B
1 001 007
2 002 045
3 070 060
4 080 050
5 100 080
6 724 124
7 803 124
8 804 125
9 910 125

The Top Left cell is A1 and the bottom right cell is B9.
=VLOOKUP(070,$A$1:$B$9,2,TRUE) would return 060
=VLOOKUP(071,$A$1:$B$9,2,TRUE) would return 060
=VLOOKUP(079,$A$1:$B$9,2,TRUE) would return 060
=VLOOKUP(079,$A$1:$B$9,2,FALSE) would return #N/A
=VLOOKUP(724,$A$1:$B$9,2,TRUE) would return 124
=VLOOKUP(725,$A$1:$B$9,2,TRUE) would return 124
=VLOOKUP(802,$A$1:$B$9,2,TRUE) would return 124

So in those examples I showed how important it is to have your ranges bracketed. For instance, the lookup values 724 through 803 have the same zone, that way you are assured to get the zone you need.

Hope that helps!

Jeff

 

[gregz1234]

Ah, makes sense. Will give this a try now. Thanks Jeff!