Race category formula

[jezzery]

Previous to this Alphafrog kindly put together the formulas below to work out whether participants received a gold, silver or bronze according to their times of 2 different distances. I now have a race that includes 3 distances and cannot work out how to include another race category into the formula that works out their award based on what time they have achieved for the distance. The data for the race is as follows if anyone can help and the previous data and formula is below.

The Standard Times for 110 miles are:

Age Group 18 to 39 = 6:10 Gold, 6:30 Silver, 6:50 Bronze.
Age Group 40 to 49 = 6:25 Gold, 6:40 Silver, 7:00 Bronze.
Age Group 50 to 59 = 6:35 Gold, 6:50 Silver, 7:10 Bronze.
Age Group 60+ = 6:50 Gold, 7:10 Silver, 7:30 Bronze.
The Standard Times for 72 miles are:
Age Group 18 to 39 = 4:20 Gold, 4:40 Silver, 5:00 Bronze.
Age Group 40 to 49 = 4:35 Gold, 4:50 Silver, 5:10 Bronze.
Age Group 50 to 59 = 4:45 Gold, 5:00 Silver, 5:20 Bronze.
Age Group 60+ = 5:00 Gold, 5:20 Silver, 5:40 Bronze.
The Standard Times for 42 miles are:
Age Group 18 to 39 = 2:20 Gold, 2:40 Silver, 3:05 Bronze
Age Group 40 to 49 = 2:30 Gold, 2:50 Silver, 3:15 Bronze
Age Group 50 to 59 = 2:40 Gold, 3:05 Silver, 3:30 Bronze
Age Group 60+ = 3:00 Gold, 3:25 Silver, 3:50 Bronze.
Ladies add 20 mins to each category

*

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

1

Name

Age

Sex

Route

Time

Award

*

80 Mile

*

*

*

*

*

50 Mile

*

*

*

2

Jim

18

M

80

5:00

Silver

*

Age

Gold

Silver

Bronze

---

*

Gold

Silver

Bronze

---

3

Jane

33

F

80

6:00

Bronze

*

18

0

4:50

5:10

5:40

*

0

2:50

3:10

3:25

4

John

44

M

50

3:00

Silver

*

40

0

5:05

5:20

5:50

*

0

3:00

3:20

3:45

5

June

55

F

50

3:29

Gold

*

50

0

5:15

5:35

6:05

*

0

3:10

3:35

4:00

6

Joan

66

F

50

4:54

---

*

60

0

5:35

6:00

6:25

*

0

3:30

3:55

4:20

7

*

*

*

*

*

*

*

*Ladies add 20 mins to each category

*

*

*

*

*

*

*

*

*

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula F2 =INDEX($I$2:$L$2,MATCH(E2-IF(UPPER(C2)="F",TIMEVALUE("00:20:00"),0),INDEX(IF(D2=80,$I$3:$L$6,$N$3:$Q$6),MATCH(B2,$H$3:$H$6,1),0),1)) F3 =INDEX($I$2:$L$2,MATCH(E3-IF(UPPER(C3)="F",TIMEVALUE("00:20:00"),0),INDEX(IF(D3=80,$I$3:$L$6,$N$3:$Q$6),MATCH(B3,$H$3:$H$6,1),0),1)) F4 =INDEX($I$2:$L$2,MATCH(E4-IF(UPPER(C4)="F",TIMEVALUE("00:20:00"),0),INDEX(IF(D4=80,$I$3:$L$6,$N$3:$Q$6),MATCH(B4,$H$3:$H$6,1),0),1)) F5 =INDEX($I$2:$L$2,MATCH(E5-IF(UPPER(C5)="F",TIMEVALUE("00:20:00"),0),INDEX(IF(D5=80,$I$3:$L$6,$N$3:$Q$6),MATCH(B5,$H$3:$H$6,1),0),1)) F6 =INDEX($I$2:$L$2,MATCH(E6-IF(UPPER(C6)="F",TIMEVALUE("00:20:00"),0),INDEX(IF(D6=80,$I$3:$L$6,$N$3:$Q$6),MATCH(B6,$H$3:$H$6,1),0),1)) <tbody> </tbody>

<tbody>
</tbody>