Finding the result when looking up a value in a array with result from a choice of colums

[letries]

I'm trying to break down a chunk formula in smaller parts.

I must find the value of time closest to time values in an array and the answer must be chosen from a different row. So here goes

Male 10
Age Cat GOLD SILVER BRONZE
< 20 00:34:00 00:36:30 00:39:00
20-34 00:34:00 00:36:30 00:39:00
35-39 00:35:30 00:38:00 00:40:00
40-49 00:36:30 00:39:00 00:41:00
50-59 00:38:00 00:41:30 00:44:00
60+ 00:40:00 00:44:30 00:48:30

Sheet 1: I have data = distance, gender, age cat and time
Sheet 2: I have above

I must find/use on Sheet 1 distance, gender, age cat and time end, look up the time on sheet 2 [the data above] and my result must be Gold, Silver of Bronze.

Example: Distance = 10; Gender = Male; Age cat = 40-49; time = 0:38:38 from Sheet 1. My result must be SILVER as 0:38:38 is slower than 0:36:30 but faster than 0:39:00

This must be done for male and female, but I think if I got the concept, I can apply it everywhere.
A mouthful, but if this possible in a formula

[JohnTopley]

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[letries]

Attached. on sheet 1 is what I need to try and do
Thanks

[JohnTopley]

I assume that the times relate to maximum allowed: so Male 40-49 in 10 KM must be less than or equal to 00:41:00 for the BRONZE i.e time 00:41:05 will not receive a "medal".

[letries]

correct, If 0:36:21 ==> Gold, if 0:38:00 ==> Silver, if 39:03 ==> Bronze.

[JeteMc]

Perhaps this will help.
This proposed solution restructures the tables on sheet 2 into records.
This proposed solution also employs a helper column on sheet 1, which may be moved and/or hidden for aesthetic purposes. The helper column formula could be embedded into the formula that populates column K, however it would require that formula to find the row value up to three times as opposed to having a helper in column R find it once.
The helper column (R) is populated using:

Please Login or Register  to view this content.

Column K, Club Std., is populated using:

Please Login or Register  to view this content.

Let us know if you have any questions.

[bebo021999]

Try to keep original format, with array fomular in K2:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.