Lookup tool to return a result based a number range

mecerrato

Board Regular
Joined
Oct 5, 2015
Messages
174
Office Version
  1. 365
Platform
  1. Windows
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?

1666141156904.png
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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)
 
Upvote 0
I'm glad you found something that works for you, but there's a much simpler way:

Excel Formula:
=VLOOKUP(C2,B7:D21,3)
 
Upvote 0
Try:
Book1
ABCD
1
2
3Lookup135
4BandI
5
6Comp BandBand
75059.99A
86069.99B
97079.99C
108089.99D
119099.99E
12100109.99H
13110119.99G
14120129.99H
15130139.99I
16140149.99J
17150159.99K
18160169.99L
19170179.99M
20180189.99N
21190199.99O
Sheet2
Cell Formulas
RangeFormula
C4C4=FILTER($D$7:$D$21,($B$7:$B$21<=C3)*($C$7:$C$21>=C3))
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

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
Back
Top