New Contributor

Help with lookup function

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

3 Replies

Re: Help with lookup function

Help me (and presumably others) interpret your score sheets. It's easy to tell apart the sections that refer to gender, but it's not clear (at least not to me) how the column and row headers refer to age....so if you could walk through in English (i.e., in words) what you would do if doing this identification of, say, minimum sit up score for male at age 21. What's the range of numbers there, how would they be identified (visually, in words) from which the minimum is to be selected? I know it's clear to you, and maybe it's something obvious that I'm missing. I suspect the formula will be fairly straightforward, but I need to know how to navigate that array of numbers.

Re: Help with lookup function

@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