That worked, thanks! Here's the next question...(the $100million question)...
That formula worked for an individual case--now I want to globalize it.
I want the PROGRAM to determine the appropriate worksheet from which to
generate the result (so I don't have to enter the worksheet names for each
age category myself).
In the case below, 'M4' referenced the worksheet that has the run scores for
MALES in age category 4 (30-34 years).
In the data that follows, M4 comes from merging the values in columns "M/F"
(Male/Female--which is a manual input, "M") and "Age Cat." [Age
Category--which is derived from the formula
=LOOKUP(G2,{17,20,25,30,35,40,45,50,55,60,65},{"1" ,"2","3","4","5","6","7","8","9","10","11"}),
"4"], having already computed the person's age (difference betw. current date
and their birthdate).
M/F Age Age Cat. CAT.
M 31 4 M4
Thanks for the help!
--NavyPianoMan


"Niek Otten" wrote:

Sort your data descending (on column G) and use this formula:

=INDEX('M4'!H2:H13,MATCH(S2,'M4'!G2:G13,-1))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"NavyPianoMan" wrote in message
...
| In the following example, the time "13:15" generates the points "65". I want
| it to generate the points "60". The formula seems to be connecting the
| lookup-value S2 (13:15) with the 65 row. I need it to chose the 60 row,
| because the time 13:15 is not fast enough to belong to the "13:00" group, but
| fits within the range of "13:01 to 13:45".
| How can I change the formula so I get the result of "60" points?
|
| My formula: =VLOOKUP(S2,'M4'!G2:H13,2)
| "S2"=13:15 (formatted as [mm]:ss)
| 'M4'!=the worksheet with the following relevant columns:
| Col'G' Col'H'
| Run Points
| 09:20 100
| 09:45 95
| 10:00 90
| 10:30 85
| 11:00 80
| 11:15 75
| 12:00 70
| 13:00 65
| 13:45 60
| 14:00 55
| 14:15 50
| 14:30 45
|