Web Applications Stack Exchange is a question and answer site for power users of web applications. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

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.