Forum Discussion

Copper Contributor

Aug 23, 2017

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 mon...

BI & Data Analytics

tips and tricks

JKPieterse
Aug 23, 2017
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))

Clemens Berthold

Copper Contributor

Aug 25, 2017

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...

JKPieterse

Silver Contributor

Aug 25, 2017

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.

Clemens Berthold
Copper Contributor
Aug 25, 2017
thx again - that does the trick :-)