I'm trying to build a formula in excel where it will look up a value (body fat percentage in this case) and return the criteria of that value. So for example, I would have two input data:
- the person's age (e.g. - 20)
- the person's body fat (e.g. - 14%)
which would return "Excellent"
I'm having a hard time coming up with this formula because I'm not sure how to write a formula that will dynamically lookup the row that pertains to the person's age and then look up that person's body fat pertaining to only that age group.
The closest thing that I came across was writing a formula for 2D table, but that doesn't serve my purposes because it returns a value according to the header and age group.
Any help would be much appreciated!
CodePudding user response：
Here is a possible solution with a more machine friendly data:
| A | B | C | D | E | F | G | H | I | J | K | L | M |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| WOMAN | WOMAN | WOMAN | WOMAN | WOMAN | WOMAN | MAN | MAN | MAN | MAN | MAN | MAN | |
| AGE (MIN) | Dangerously low | Excellent | Good | Fair | Poor | Dangerously high | Dangerously low | Excellent | Good | Fair | Poor | Dangerously high |
| 20 | 0 | 14 | 16,6 | 19,5 | 22,8 | 27,2 | 0 | 8 | 10,6 | 14,9 | 18,7 | 23,2 |
| 30 | 0 | 14 | 17,5 | 20,9 | 24,7 | 29,2 | 0 | 8 | 14,6 | 18,3 | 21,4 | 25 |
| 40 | 0 | 14 | 19,9 | 23,9 | 27,7 | 31,3 | 0 | 8 | 17,5 | 20,7 | 23,5 | 26,7 |
| 50 | 0 | 14 | 22,6 | 27,1 | 30,5 | 34,6 | 0 | 8 | 19,2 | 22,2 | 24,7 | 27,9 |
| 60 | 0 | 14 | 23,3 | 28 | 31,4 | 35,5 | 0 | 8 | 19,8 | 22,7 | 25,3 | 28,5 |
| Gender | Age | Body's fat | Response | |||||||||
| woman | 20 | 14 | =INDEX(B2:M2,MATCH(C10,OFFSET(B2:G2,MATCH(B10,A3:A7,1),MATCH(A10,B1:M1,0)-1),1) MATCH(A10,B1:M1,0)-1) |
Copy this table (without the headers) in the cell A1 of a new sheet. The formula you are looking for is in cell D10 (it's the only formula and it's located under the cell with Response).
You might also apply a conditional formatting to the table (range $B$3:$M$7) using this formula:
=AND(ROW(B3)-ROW($B$3) 1=MATCH($B$10,$A$3:$A$7,1),COLUMN(B3)-COLUMN($B$3) 1=MATCH($C$10,OFFSET($B$2:$G$2,$D$13,MATCH($A$10,$B$1:$M$1,0)-1),1) MATCH($A$10,$B$1:$M$1,0)-1)
to highlight the exact target you are focusing.
Just for fun, this formula has all the data hardcoded in it:
=CHOOSE(AGGREGATE(14,6,1/(((LEFT(1/(LEFT({"F 020 0000"\"F 020 1400"\"F 020 1660"\"F 020 1950"\"F 020 2280"\"F 020 2720"."F 030 0000"\"F 030 1400"\"F 030 1750"\"F 030 2090"\"F 030 2470"\"F 030 2920"."F 040 0000"\"F 040 1400"\"F 040 1990"\"F 040 2390"\"F 040 2770"\"F 040 3130"."F 050 0000"\"F 050 1400"\"F 050 2260"\"F 050 2710"\"F 050 3050"\"F 050 3460"."F 060 0000"\"F 060 1400"\"F 060 2330"\"F 060 2800"\"F 060 3140"\"F 060 3550"."F 999 0000"\"F 999 1400"\"F 999 2330"\"F 999 2800"\"F 999 3140"\"F 999 3550"."M 020 0000"\"M 020 0800"\"M 020 1060"\"M 020 1490"\"M 020 1870"\"M 020 2320"."M 030 0000"\"M 030 0800"\"M 030 1460"\"M 030 1830"\"M 030 2140"\"M 030 2500"."M 040 0000"\"M 040 0800"\"M 040 1750"\"M 040 2070"\"M 040 2350"\"M 040 2670"."M 050 0000"\"M 050 0800"\"M 050 1920"\"M 050 2220"\"M 050 2470"\"M 050 2790"."M 060 0000"\"M 060 0800"\"M 060 1980"\"M 060 2270"\"M 060 2530"\"M 060 2850"."M 999 0000"\"M 999 0800"\"M 999 1980"\"M 999 2270"\"M 999 2530"\"M 999 2850"},5)=IF(A10="woman","F","")&IF(A10="man","M","")&" "&TEXT(CHOOSE(MATCH(B10,{20.30.40.50.60.999},1),20,30,40,60,999),"000")),0)&RIGHT({"F 020 0000"\"F 020 1400"\"F 020 1660"\"F 020 1950"\"F 020 2280"\"F 020 2720"."F 030 0000"\"F 030 1400"\"F 030 1750"\"F 030 2090"\"F 030 2470"\"F 030 2920"."F 040 0000"\"F 040 1400"\"F 040 1990"\"F 040 2390"\"F 040 2770"\"F 040 3130"."F 050 0000"\"F 050 1400"\"F 050 2260"\"F 050 2710"\"F 050 3050"\"F 050 3460"."F 060 0000"\"F 060 1400"\"F 060 2330"\"F 060 2800"\"F 060 3140"\"F 060 3550"."F 999 0000"\"F 999 1400"\"F 999 2330"\"F 999 2800"\"F 999 3140"\"F 999 3550"."M 020 0000"\"M 020 0800"\"M 020 1060"\"M 020 1490"\"M 020 1870"\"M 020 2320"."M 030 0000"\"M 030 0800"\"M 030 1460"\"M 030 1830"\"M 030 2140"\"M 030 2500"."M 040 0000"\"M 040 0800"\"M 040 1750"\"M 040 2070"\"M 040 2350"\"M 040 2670"."M 050 0000"\"M 050 0800"\"M 050 1920"\"M 050 2220"\"M 050 2470"\"M 050 2790"."M 060 0000"\"M 060 0800"\"M 060 1980"\"M 060 2270"\"M 060 2530"\"M 060 2850"."M 999 0000"\"M 999 0800"\"M 999 1980"\"M 999 2270"\"M 999 2530"\"M 999 2850"},4))*1)<=C10*100)*{1\2\3\4\5\6},1),"Dangerously low","Excellent","Good","Fair","Poor","Dangerously high")
It assumes your data (gender, age and body's fat) are still stored as in the previous solution (cell A10, B10, C10). Of course i raccomend the first method, but still...