Lookup number within a range and return zone.

[ZeDoctor]

Hi Guys,

I need to be able to do a lookup for a number in a range.Like the below table, so if the number was 24 it would pick up Zone 3?

Any ideas?

Cheers,

60-69	Zone 1
99	Zone 1
10-19	Zone 2
30-39	Zone 2
20-29	Zone 3

[Tony Valko]

Are those the real number ranges or are they just made up for the post?

[Richard Buttrey]

What about the gaps e.g.

0-9
40-59
70-98
Over 99

[ZeDoctor]

Sorry guys,

Made it smaller to make it simpler but it messed it up didnt it.

The real range is

60-69	Zone 1
99	Zone 1
10-19	Zone 2
30-39	Zone 2
20-29	Zone 3
40-59	Zone 3
70-98	Zone 3

[jhren]

See attached...

[ZeDoctor]

Thanks man.

Do I just use a standard vlookup formula on this now then?

Can I ask the method behind it as well as i've got quite a few zones I need to do this for.

Thanks again

[ZeDoctor]

Okay ignore that vlookup question, I'm an idiot, I've just seen what you've done. The methodology would help though please

[Richard Buttrey]

Hi,

See attached

[Tony Valko]

Setup your table as show:

Data Range

	A	B	C	D	E	F
1	Number	Location	------	From	To	Location
2	74	Zone 3		10	19	Zone 2
3				20	29	Zone 3
4				30	39	Zone 2
5				40	59	Zone 3
6				60	69	Zone 1
7				70	98	Zone 3
8				99	99+	Zone 1

This formula entered in B2:

=IFERROR(LOOKUP(A2,D2:F8),"")