May 21 2021 12:00 AM
I am running Windows 10 and Office 2016.
In the files attached I am needing to look up the minimum push up (PU MIN), minimum sit up score (SU MIN), & the maximum time for the 2 mile run (Max Time). The requirement is 60% in each exercise and that is based on the age and gender. I have all that info on the sheet I can't seem to find the correct formula to reference the gender and age and then lookup what a 60% score
May 21 2021 03:56 PM
May 21 2021 07:39 PM
@jletendre12 here is 1 solution:
=LET(tbl,CHOOSE(1+($C2="F"),M_PU_Scores,F_PU_Scores),v,XLOOKUP(60,INDEX(tbl,,MATCH($E2,VALUE(OFFSET(tbl,-1,0,1)))),OFFSET(tbl,,-1,,1),"na",1),v)
and then update M_PU_Scores and F_PU_Scores for each category (see attached sheet)
so I made the formula to 'fit' the named ranges you already had, but it turned out I had to fix all those names because they were referring to your local sheet (i'm guessing you made a copy but the names still ref the original workbook with slightly different sheet names). The formula is a little wonky because I use offset with -1 to get the header (age) row and the row index column (count).
In any case if you want to know what is happening, the LET statement allows me to define a variable and in case that is to set tbl to either the male or female table. next I set 'v' to the answer and lastly I display V. As for how i calc 'v' it is a lookup for the value of 60 in the column that matches the age and then return the count.
Hope this works :)