# How do I select Column1's value if an input value is between the integers in Columns2:3 for a range?

In a Google Sheets I have two tables:

``````Date        BMI     BMI Outcome
2011-06-21  22.1    ?
2011-06-22  21.8    ?
2011-06-23  21.5    ?
``````

and

``````Name                    From    To
Severely underweight            15.99
Underweight             16      18.49
Normal                  18.5    24.99
Overweight              25      29.99
Obese Class I           30      34.99
Obese Class II          35      39.99
Obese Class III         40
``````

A blank value in `From` is assumed to be `0` and a blank value in `To` is assumed to be infinity.

I want to write a formula to enter into the `BMI Outcome` column of the first table that will take the value from the `BMI` column, and look up the corresponding `Name` from Table2 for whichever "From-To" range the `BMI` value lies between.

So for example `22.1` should have an outcome of `Normal`.

This will work. I've used the Named Ranges, Name and From. You don't have to use To at all, since your values are all contiguous. B2 is what I used as the value of the first BMI, you can just drag this formula down the column:

``````=IFERROR(INDEX(Name, MATCH(B2,From),1),INDEX(Name,1,1))
``````
• Brilliant, thanks Lance - works perfectly :)
– user11720
Commented Jun 23, 2011 at 14:22
• @Nick, You should be able to associate your account so that the question is tied to you. Commented Jun 23, 2011 at 14:27
• @Nick - you should register your accounts here and on Super User then you can associate them and you'll regain ownership of the question.
– ChrisF
Commented Jun 23, 2011 at 14:57

I suggest simpler merely to add a `0` into the From cell for Severely underweight, then:

``````=INDEX(Name,MATCH(B2,From))
``````

to avoid the IFERROR function (which in @Lance's answer may not be working as intended - maybe `INDEX(Name,2)` rather than `INDEX(Name,1,1)` if to avoid adding `0`).