Lookup tool to return a result based a number range

[mecerrato]

I have this simple sheet and I want to return the band that the lookup value falls into.
In this example I would expect the band to be returned to be "I" because 135 falls in the range of 130 to 139.99

I just can't figure out what formula to use, I tried a variation of lookup and index match but cannot get it to work

Can someone help me out?

 

[mecerrato]

I have this simple sheet and I want to return the band that the lookup value falls into.
In this example I would expect the band to be returned to be "I" because 135 falls in the range of 130 to 139.99

I just can't figure out what formula to use, I tried a variation of lookup and index match but cannot get it to work

Can someone help me out?

View attachment 76489

I am happy to report that I used this formula and it worked
=LOOKUP(2,1/(Sheet2!$B$7:$B$21<=C3)/(Sheet2!$C$7:$C$21>=C3),Sheet2!$D$7:$D$21)

 

[Eric W]

I'm glad you found something that works for you, but there's a much simpler way:

=VLOOKUP(C2,B7:D21,3)

 

[AhoyNC]

Try:

Book1
	A	B	C	D
1
2
3		Lookup	135
4		Band	I
5
6		Comp Band		Band
7		50	59.99	A
8		60	69.99	B
9		70	79.99	C
10		80	89.99	D
11		90	99.99	E
12		100	109.99	H
13		110	119.99	G
14		120	129.99	H
15		130	139.99	I
16		140	149.99	J
17		150	159.99	K
18		160	169.99	L
19		170	179.99	M
20		180	189.99	N
21		190	199.99	O
Sheet2

Cell Formulas
Range	Formula
C4	C4	=FILTER($D$7:$D$21,($B$7:$B$21<=C3)*($C$7:$C$21>=C3))