Hi, I have tables of Body Mass Index (BMI) for boys and Girls containing matrices of BMI categories (Normal, Overweight...). On a separate table is a list of names with corresponding information of sex, age and BMI. Based on the data provided in cells A1415, please help me with a formula to return the category if a person is severely wasted, wasted, normal, overweight or obese in E14 and E15. Thank you. Sheet attached.

Attached Files

BMI.xlsx (24.0 KB, 13 views)

Unfortunate data layout, but here's one way: in E14:

=IF(B14="F",IF(COUNTIF(D14,INDEX(Severely_Wasted,M ATCH(C14,Month,0))),"Severely wasted",IF(COUNTIF(D14,INDEX(Obese,MATCH(C14,Month ,0))),"Obese",CHOOSE(ROUND(MATCH(D13,INDEX(tBMIGir ls[[From]:[To3]],MATCH(C14,Month,0),0))/2,0),"Wasted","Normal","Overweight"))),IF(COUNTIF( D14,INDEX(tBMIBoys[[Severely Wasted if BMI is ]],MATCH(C14,tBMIBoys[Age in Months],0))),"Severely wasted",IF(COUNTIF(D14,INDEX(tBMIBoys[[Obese ]],MATCH(C14,tBMIBoys[Age in Months],0))),"Obese",CHOOSE(ROUND(MATCH(D14,INDEX(tBMIBoy s[[From]:[To3]],MATCH(C14,tBMIBoys[Age in Months],0),0))/2,0),"Wasted","Normal","Overweight"))))

and copy down.

Thank you Debaser. It took me sometime to reply because when I copied the formula and edited it to fit into my actual data, a lot of N/A came out, caused by children below 72 months of age. I did not notice that there is a separate growth table for infants to toddlers. I will try adding another IFs in the formula that you gave. I hope I'll get it right.

A simpler solution in attached workbook.

Attached Files

BMI.xlsx (24.8 KB, 16 views)

I'm done completing the regrouping of the BMI data per age in months and per category and the table ran to 2800 rows (age 48 months to 228 months or 5 to 19 years old) but it is a lot simpler than the formula in post #2. Again, thank you.

I myself too spent most of time editing BMI table for example. The actual formula did take a couple of minutes