CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
Athanatos
Athanatos

Reputation: 1089

Return the classification automatically using DOB and range of numbers

For the following table below I would like to input the classification(I4-I10) on (G2) automatically based on the date of birth of the user(C2) and the number of reps that they did F2 compared to the range of repetitions for the appropriate age group.

enter image description here

Thanks

Upvotes: 0

Views: 163

Answers (1)

user3514930
user3514930

Reputation: 1717

I think it's better to change the scheme with only the lower value, like:

enter image description here

In that case case it's only a sequence of Loopup/Match...
The formula can be:

=INDEX(I4:I10;MATCH(F2;OFFSET(I4:I10;0;MATCH(ROUND((TODAY()-C2)/365;0);J3:O3;1));1))

Splitted you have:

=MATCH(ROUND((TODAY()-C2)/365;0);J3:O3;1)     -> [P3] To find the age and than the Index of the column to use
=MATCH(F2;OFFSET(I4:I10;0;P3);1)      -> [Q4] To find the Index in the column of P3.
=INDEX(I4:I10;Q4)      -> Recover the text.

Upvotes: 1

Related Questions