Lookup Value From Range Within Another Cell. Help!

gregz1234

New Member
Joined
Mar 16, 2016
Messages
28
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).

AB
Dest ZipZone
004-005

<colgroup><col><col></colgroup><tbody>
</tbody>
007
006-007045
009045

<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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 16, 2016
Messages
28
I will give that a try. thanks!

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

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
It will. Once the value is higher then the lookup value, it returns the last row.
 

gregz1234

New Member
Joined
Mar 16, 2016
Messages
28

ADVERTISEMENT

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

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,326
Messages
5,595,519
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top