Find Value based on user criteria

I had this question answered earlier but I change the format and cant figure out how to manipulate it to work with this new format.

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
macone1976Asked:
Who is Participating?
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.

Martin LissOlder than dirtCommented:
Well you don't have green cells but I assume you mean rows 4 to 35.

Do you want a macro or a formula?
0
macone1976Author Commented:
Martin, sorry yes rows 4 to 35, looking for a formula.
0
Martin LissOlder than dirtCommented:
If the run time were any value from 11.24 to 11.39 would the score still be 56.3?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

macone1976Author Commented:
For a Male at 39 it would 52.4 if he ran between 11.24 and 11.39, if it was a female it would be it would be 58.6
0
Martin LissOlder than dirtCommented:
Here's a user defined function.

Usage:
=getscore(A2,B2,C2)

Function GetScore(G As Variant, A As Variant, T As Variant) As Variant

Dim wsData As Worksheet
Dim lngLastRowD As Long
Dim lngRowD As Long
Dim intCol As Integer

Set wsData = Sheets("Sheet2")

lngLastRowD = wsData.Range("A1048576").End(xlUp).Row

Select Case UCase(G)
    Case "M"
        Select Case A
            Case Is < 30
                intCol = 2
            Case 30 To 39
                intCol = 3
            Case 40 To 49
                intCol = 4
            Case 50 To 59
                intCol = 5
            Case Else
                intCol = 6
        End Select
    Case "F"
        Select Case A
            Case Is < 30
                intCol = 7
            Case 30 To 39
                intCol = 8
            Case 40 To 49
                intCol = 9
            Case 50 To 59
                intCol = 10
            Case Else
                intCol = 11
        End Select
    Case Else
        MsgBox "Invalid Gender '" & G & "' found"
        Exit Function
End Select
For lngRowD = 4 To lngLastRowD
    If CCur(T) <= wsData.Cells(lngRowD, 1) Then
        Exit For
    End If
Next
GetScore = wsData.Cells(lngRowD, intCol)
End Function

Open in new window

0

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
macone1976Author Commented:
Thank You Martin, it works perfectly.
0
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.