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

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

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.

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?
macone1976Incident ResponderAuthor Commented:
Martin, sorry yes rows 4 to 35, looking for a formula.
Martin LissOlder than dirtCommented:
If the run time were any value from 11.24 to 11.39 would the score still be 56.3?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

macone1976Incident ResponderAuthor 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
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

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:
Thank You Martin, it works perfectly.
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
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.