ExcelBanter - assign value to range

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - assign value to range (https://www.excelbanter.com/excel-programming/361638-assign-value-range.html)

assign value to range

I have a column with the first three numbers of a zip code. Each of these zip
codes is in a particular zone, but it's not as easy as 00001-00004 = zone 1,
00005-00010 = zone 2...they're all over the place.

I need somehow to have in the zone column a function that says if the number
zip column falls into a particular set of ranges then zone # will = 2-12.

I just don't know how to set up a bunch of ranges. Without knowing how to
do it...my mind would say do something like IF(B1 = {001,300}, {467,567},
{855, 964}), "zone 1" - with the ranges being 001 to 300, 467 to 567, and so
on.

I know that's not the way to do it, of course...but that's what I need to
know.

Thanks so much

assign value to range

What you describe is called a lookup table. Apparently someone else is
struggling with a similar problem in an earlier thread under the name
rockoffaith. Maybe you should consult with them although they don't seem to
have much more to offer in the form of specifics than you.

Using an if statement would work if you only have 7 different conditions.

=IF(Or(And(B1 1 and B1 < 300), And(B1467,B1<567),And(B1855,B1<964)),
"zone 1",If(or(And(B1=300,B1<400),And(B1=567,B1<=600)) ,"zone2,If( . . .

--
Regards,
Tom Ogilvy

"Justin" wrote:

I have a column with the first three numbers of a zip code. Each of these zip
codes is in a particular zone, but it's not as easy as 00001-00004 = zone 1,
00005-00010 = zone 2...they're all over the place.

I need somehow to have in the zone column a function that says if the number
zip column falls into a particular set of ranges then zone # will = 2-12.

I just don't know how to set up a bunch of ranges. Without knowing how to
do it...my mind would say do something like IF(B1 = {001,300}, {467,567},
{855, 964}), "zone 1" - with the ranges being 001 to 300, 467 to 567, and so
on.

I know that's not the way to do it, of course...but that's what I need to
know.

Thanks so much

assign value to range

Yeah...I'm the rockofaith guy. I figured I'd ask on the site that
looked like the microsoft site...but it put the question here.
Interesting.

--
rockofaith
------------------------------------------------------------------------
rockofaith's Profile: http://www.excelforum.com/member.php...o&userid=34494
View this thread: http://www.excelforum.com/showthread...hreadid=542628

assign value to range

Hi Justin

I suggest you use something like this:

Create a list of codes and zones like, keeping at least one row free at
the top:

___|__A__|___B____|
_1_|_____|________|
_2_|_001_|_Zone_1_|
_3_|_301_|_Zone_2_|
_4_|_467_|_Zone_1_|
_5_|_568_|_Zone_3_|
_6_|_855_|_Zone_1_|
_7_|_964_|_Zone_2_|

The function for the Zone Column would be:
Assuming the Zip code is in Cell D1
=OFFSET($B$1,MATCH(D1,$A$2:$A$7,1),0)

HTH,

Executor

assign value to range

the email I see for Crowbar is split up...is it prec_tec?

Executor...I don't see a range capability

--
rockofait
-----------------------------------------------------------------------
rockofaith's Profile: http://www.excelforum.com/member.php...fo&userid=3449
View this thread: http://www.excelforum.com/showthread.php?threadid=54262

assign value to range

Hi,

You can simply copy my function down.
The function will change into
=OFFSET($B$1,MATCH(D1,$A$2:$A$7,1),0)
=OFFSET($B$1,MATCH(D2,$A$2:$A$7,1),0)
=OFFSET($B$1,MATCH(D3,$A$2:$A$7,1),0)
=OFFSET($B$1,MATCH(D4,$A$2:$A$7,1),0)
and so on...

Makes this things clear?

Executor

assign value to range

Hi,

Your email looks not complete.
In my first reply you see the upperleft corner of a sheet.
Format column A as text.
Fill is the zipcodes.
Fill column B with the zones

Fill column D with the zipcoes you want indicated
Fill column E with my formula.

Greetings

Executor