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    ?


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))
| improve this answer | |
  • 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:


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).

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy