# In Google Spreadsheets how do I select column 1's value if an input value is between the integers in colums 2 and 3 for a range?

``````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 table 2 for whichever "From-To" range the "BMI" value lies between.

So for example the 22.1 column should have an outcome of 'Normal'.

-

## migrated from superuser.comJun 23 '11 at 1:13

This question came from our site for computer enthusiasts and power users.

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 Jun 23 '11 at 14:22
@Nick, You should be able to associate your account so that the question is tied to you. – Lance Roberts Jun 23 '11 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 Jun 23 '11 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`).

-