Forum Discussion
Solved
Match Value to Category
I have a table with Body-Mass-Index Percentiles for different agegroups (http://www.who.int/growthref/bmi_boys_perc_WHO2007_exp.txt)
I also have a list of children from whom i know the age in months (like in the first column) of the table and the exact BMI - I want to know which percentil category each child is in
I already have an idea to at least find the right percentil cut off (which i would encode the category in) but excel keeps making errors idea would be: =HLOOKUP("BMI";B5:P169;age-60;TRUE)
it startet putting out right values for about 10 kids and then startet to make errors, giving out vaues from the right row, but not the one closest to the searches BMI value
thx for your help
cheers Clemi
I pasted the txt file in cell A1. That fills the range A1:S169 with the table.
With Month in cell V1 and BMI in V2, this formula seems to do it:
=INDEX($E$1:$S$1,MATCH(V2,OFFSET($E$2:$S$169,MATCH(V1,$A$2:$A$169,0),0,1),1))
I pasted the txt file in cell A1. That fills the range A1:S169 with the table.
With Month in cell V1 and BMI in V2, this formula seems to do it:
=INDEX($E$1:$S$1,MATCH(V2,OFFSET($E$2:$S$169,MATCH(V1,$A$2:$A$169,0),0,1),1))
Hey Jan,
thx for the help, the formula is delivering the desiered answers (name of the culumn)
sadly the answers are, after the first two or three, incorrect, could that come from an cummulation error when copying the formula to the whole table of BMI and Age values? or is it an error in the MATCH function?
Basically its the same problem i had with my approach, in that case the HLOOKUP funktion
It seems to make errors in finding the right value to the one we look for (BMI), the rest in which row or column to look for results seem to work fine...
- The second MATCh function currently does an exact match. If your data to be looked up has e.g. decimals, it will return #NA for non-whole numbers. Changing the third argument of the second MATCH to 1 should work.
