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
macone1976Incident ResponderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Glenn RayExcel 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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
macone1976Incident ResponderAuthor Commented:
Glenn, thanks.  I asked another question with a changed format
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.