Google-sheets – How to select Column1’s value if an input value is between the integers in Columns2:3 for a range

google sheetsworksheet-function

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.

Best Answer

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))
Related Topic