Search code examples
excellookuplookup-tables

Excel help: How to fill a cell with data from a table dependent on multiple variables

I have a report sheet I have made on excel that has age, gender and skinfold measurements (in mm) and two tables displaying body fat percentages depending on skinfold measurement, age and gender.

I would like a cell to report the percentage of bodyfat depending on other cells (age, gender, skinfold)

So far I have the following equation: 

=IF(AND(E8="MALE",B8<=19),LOOKUP(B18,Table2[17-19]))

With B18 = 9.2

The (male) table being looked up has skinfold in mm in the left column, and age ranges across the top (17-19, 20-29, 30-39, 40-49, 50+). At the moment the formula finds the closest value (10) in the age column 17-19, however I want the formula to find skinfold first then depending on what age range turn out the corresponding percentage.

The cell highlighted has the current formula in.

Print Screen of Report

Solution 
  • =IF(OR(B18<10,MOD(B18,2)<>0)," < ","") & INDEX(IF(E8="FEMALE",L5:P20,T5:X20),MATCH(INT(B18/2+0.95)*2,K5:K20,0),IF(AND(B8>=17,B8<=19),1,IF(AND(B8>=20,B8<=29),2,IF(AND(B8>=30,B8<=39),3,IF(AND(B8>=40,B8<=49),4,IF(B8>=50,5))))))

    This seems to do it. In an overview.. :

    1. If "FEMALE" look in L5:P20 else T5:X20 .

    2. Match the age. (select row) if 9.2 convert to 10, 10.1 -> 12, 11.0 -> 12 by using INT(B18/2+0.95)*2

    3. Lastly using the range defined, identify in which column @ L5:P20 or T5:X20 data range should the value be taken from.

    4. display " < " if there is no exact match using IF(OR(B18<10,MOD(B18,2)<>0)," < ","")

    I don't have the full dataset to test.. hope you verify and share if it works (or not).

    ( :