# Finding Values Based off of User Criteria

On sheet 1 the user enters the Gender, Age, and Time, then a forumula will compute the score.  On sheet 2, row 2 is age block and the cells in green is the time.  So an example of Male, 42, 11.39 run time, would be 56.3.  Attached is example workbook
Book2.xlsx
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Excel VBA DeveloperCommented:
It's not pretty, but this formula will work:

D2:  =IFERROR(OFFSET(Sheet2!\$A\$2,MATCH(C2,OFFSET(Sheet2!\$A\$3,0,IF(A2="M",0,10)+MATCH(B2,{0,0,30,30,40,40,50,50,60,60},1)-2,25,1),1),IF(A2="M",0,10)+MATCH(B2,{0,0,30,30,40,40,50,50,60,60},1)-1),60)

Could be simplified in any number of ways.  If the time table had all possible breakpoint times only in column A and a cascading set of qualifying scores for sex/age group (as in your previous pushup example), the formula might be a little more straightforward.

One minor issue regards how intermediate times are to be scored.  Currently, if you enter a time between two breakpoints, the higher score is awarded.

Example file attached.

Regards,
-Glenn
EE-Q-28675935.xlsx

Experts Exchange Solution brought to you by