Lookup values if Value 1 is x or y and Value 2 is a,b,c,d.

[Pablito77AU]

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

 

[T. Valko]

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,INDEX(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

 

[Teethless mama]

or this:

=VLOOKUP(B4,INDIRECT(B2),MATCH(B3,D2:I2,))

 

[Teethless mama]

correction:

=VLOOKUP(B4,INDIRECT(B2),MATCH(B3,D2:I2,),)