# 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
###### 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.

Older 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?
Incident ResponderAuthor Commented:
Martin, sorry yes rows 4 to 35, looking for a formula.
Older than dirtCommented:
If the run time were any value from 11.24 to 11.39 would the score still be 56.3?
Incident 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
Older 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
``````

Experts Exchange Solution brought to you by

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

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