Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

In a Google Docs spreadsheet 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 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'.

share|improve this question

migrated from superuser.com Jun 23 '11 at 1:13

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

2 Answers 2

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))
share|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:

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.