Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup values if Value 1 is x or y and Value 2 is a,b,c,d.
Hi There, just after a little help with an excel formula, I've tried nesting
a couple of VLookup's but to no avail. Here is the question: I have an SpreadSheet with a cell which I want to define body fat percentage based on the sum of skin fold numbers. This is defined as "Male/Female and "Age brackets" in two drop down lists with the ranges pulled from another sheet: Testings!B2 -Male -Female Testings!B3 -17-19 -20-29 -30-39 -40-49 -50+ Then I have another worksheet with the lists as such: Sum of Skinfolds Males (age in years) (mm)~~~~17-19~~~~~20-29 30-39~~~~40-49~~~50+ 15~~~~~~~5~~~~~~~~4.64~~~9.09~~~~~~8.47 ~~8.38 16~~~~~~5.75~~~~~~~5.58~~~9.74~~~~~~9.31~~9.31 17~~~~~~6.44~~~~~~~6.08~~~10.35~~~~10.09~~10.19 Sum of Skinfolds Female (age in years) (mm)~~~~17-19~~~~~20-29 30-39~~~~40-49~~~50+ 15~~~~~~~10.4~~~~~~10.22~~13.5~~~~~16.4~~~17.85 etc. So what I want to achieve is a the value for the % where age and sex are factored. i.e. If Male and 20-29 and Sum of Skinfolds is 16mm then the value is 5.58 If Female and 40-49 and Sum of Skinfolds is 15mm then the value is 16.4 Any help with the formula/s would be of great help, I just can't seem to be able to determine how to lookup the table. Thanks in advance. Pablito |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup values if Value 1 is x or y and Value 2 is a,b,c,d.
Try this...
D2:I5 = table for males and is given the named range Male. D7:I10 = table for females and is given the named range Female B2 = male or female B3 = age group B4 = skin fold number =VLOOKUP(B4,IF(B2="male",Male,Female),MATCH(B3,IND EX(Male,1,0),0),0) Here's a small sample file so that you can actually see it in practice: xPablito.xls 14kb http://cjoint.com/?hEgeNgPqPf -- Biff Microsoft Excel MVP "Pablito77AU" wrote in message ... Hi There, just after a little help with an excel formula, I've tried nesting a couple of VLookup's but to no avail. Here is the question: I have an SpreadSheet with a cell which I want to define body fat percentage based on the sum of skin fold numbers. This is defined as "Male/Female and "Age brackets" in two drop down lists with the ranges pulled from another sheet: Testings!B2 -Male -Female Testings!B3 -17-19 -20-29 -30-39 -40-49 -50+ Then I have another worksheet with the lists as such: Sum of Skinfolds Males (age in years) (mm)~~~~17-19~~~~~20-29 30-39~~~~40-49~~~50+ 15~~~~~~~5~~~~~~~~4.64~~~9.09~~~~~~8.47 ~~8.38 16~~~~~~5.75~~~~~~~5.58~~~9.74~~~~~~9.31~~9.31 17~~~~~~6.44~~~~~~~6.08~~~10.35~~~~10.09~~10.19 Sum of Skinfolds Female (age in years) (mm)~~~~17-19~~~~~20-29 30-39~~~~40-49~~~50+ 15~~~~~~~10.4~~~~~~10.22~~13.5~~~~~16.4~~~17.85 etc. So what I want to achieve is a the value for the % where age and sex are factored. i.e. If Male and 20-29 and Sum of Skinfolds is 16mm then the value is 5.58 If Female and 40-49 and Sum of Skinfolds is 15mm then the value is 16.4 Any help with the formula/s would be of great help, I just can't seem to be able to determine how to lookup the table. Thanks in advance. Pablito |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup values if Value 1 is x or y and Value 2 is a,b,c,d.
or this:
=VLOOKUP(B4,INDIRECT(B2),MATCH(B3,D2:I2,)) "Pablito77AU" wrote: Hi There, just after a little help with an excel formula, I've tried nesting a couple of VLookup's but to no avail. Here is the question: I have an SpreadSheet with a cell which I want to define body fat percentage based on the sum of skin fold numbers. This is defined as "Male/Female and "Age brackets" in two drop down lists with the ranges pulled from another sheet: Testings!B2 -Male -Female Testings!B3 -17-19 -20-29 -30-39 -40-49 -50+ Then I have another worksheet with the lists as such: Sum of Skinfolds Males (age in years) (mm)~~~~17-19~~~~~20-29 30-39~~~~40-49~~~50+ 15~~~~~~~5~~~~~~~~4.64~~~9.09~~~~~~8.47 ~~8.38 16~~~~~~5.75~~~~~~~5.58~~~9.74~~~~~~9.31~~9.31 17~~~~~~6.44~~~~~~~6.08~~~10.35~~~~10.09~~10.19 Sum of Skinfolds Female (age in years) (mm)~~~~17-19~~~~~20-29 30-39~~~~40-49~~~50+ 15~~~~~~~10.4~~~~~~10.22~~13.5~~~~~16.4~~~17.85 etc. So what I want to achieve is a the value for the % where age and sex are factored. i.e. If Male and 20-29 and Sum of Skinfolds is 16mm then the value is 5.58 If Female and 40-49 and Sum of Skinfolds is 15mm then the value is 16.4 Any help with the formula/s would be of great help, I just can't seem to be able to determine how to lookup the table. Thanks in advance. Pablito |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup values if Value 1 is x or y and Value 2 is a,b,c,d.
correction:
=VLOOKUP(B4,INDIRECT(B2),MATCH(B3,D2:I2,),) "Teethless mama" wrote: or this: =VLOOKUP(B4,INDIRECT(B2),MATCH(B3,D2:I2,)) "Pablito77AU" wrote: Hi There, just after a little help with an excel formula, I've tried nesting a couple of VLookup's but to no avail. Here is the question: I have an SpreadSheet with a cell which I want to define body fat percentage based on the sum of skin fold numbers. This is defined as "Male/Female and "Age brackets" in two drop down lists with the ranges pulled from another sheet: Testings!B2 -Male -Female Testings!B3 -17-19 -20-29 -30-39 -40-49 -50+ Then I have another worksheet with the lists as such: Sum of Skinfolds Males (age in years) (mm)~~~~17-19~~~~~20-29 30-39~~~~40-49~~~50+ 15~~~~~~~5~~~~~~~~4.64~~~9.09~~~~~~8.47 ~~8.38 16~~~~~~5.75~~~~~~~5.58~~~9.74~~~~~~9.31~~9.31 17~~~~~~6.44~~~~~~~6.08~~~10.35~~~~10.09~~10.19 Sum of Skinfolds Female (age in years) (mm)~~~~17-19~~~~~20-29 30-39~~~~40-49~~~50+ 15~~~~~~~10.4~~~~~~10.22~~13.5~~~~~16.4~~~17.85 etc. So what I want to achieve is a the value for the % where age and sex are factored. i.e. If Male and 20-29 and Sum of Skinfolds is 16mm then the value is 5.58 If Female and 40-49 and Sum of Skinfolds is 15mm then the value is 16.4 Any help with the formula/s would be of great help, I just can't seem to be able to determine how to lookup the table. Thanks in advance. Pablito |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
i wish to lookup values in column A, & add adjacent values in colu | Excel Discussion (Misc queries) | |||
Lookup with two lookup values | Excel Discussion (Misc queries) | |||
How do I use LOOKUP to return a range of values, then SUM values? | Excel Worksheet Functions | |||
Advanced Lookup (lookup for 2 values) | Excel Worksheet Functions | |||
How do I lookup and return different values when the lookup value. | Excel Discussion (Misc queries) |