Good day,
I have in Sheet 1 the following data: in Column j1:AD7
In Sheet2 column I2:j500
I want to insert a formula that looks firstly at Sheet 2 Column I matches in to Sheet 1 and then looks at Sheet 2 Column J and allocates the correct points. In order to allocate point the HMS must be under the toime to achieve the point so for SM in order to get 5 points you need a time of under 00:36:00.
So for example:
I have in Sheet 1 the following data: in Column j1:AD7
| 10 Km | ||||||||||||||||||||
| PTS | JM | SM | 35-39M | 40-44M | 45-49M | 50-54M | 55-59M | 60-64M | 65-69M | 70+M | JF | SF | 35-39F | 40-44F | 45-49F | 50-54F | 55-59F | 60-64F | 65-69F | 70+F |
| 5 | 00:36:00 | 00:36:00 | 00:37:10 | 00:38:20 | 00:40:10 | 00:42:00 | 00:43:50 | 00:45:40 | 00:47:30 | 00:49:20 | 00:40:30 | 00:40:30 | 00:41:55 | 00:43:20 | 00:45:30 | 00:47:40 | 00:49:50 | 00:52:00 | 00:54:10 | 00:56:20 |
| 4 | 00:38:00 | 00:38:00 | 00:39:05 | 00:40:10 | 00:41:55 | 00:43:35 | 00:45:20 | 00:47:00 | 00:48:45 | 00:50:30 | 00:42:10 | 00:42:10 | 00:43:30 | 00:44:50 | 00:46:50 | 00:48:55 | 00:50:55 | 00:53:00 | 00:55:00 | 00:57:00 |
| 3 | 00:41:45 | 00:41:45 | 00:42:45 | 00:43:40 | 00:45:10 | 00:46:40 | 00:48:10 | 00:49:35 | 00:51:05 | 00:52:35 | 00:45:25 | 00:45:25 | 00:46:35 | 00:47:45 | 00:49:30 | 00:51:13 | 00:53:00 | 00:54:45 | 00:56:30 | 00:58:20 |
| 2 | 00:49:20 | 00:49:20 | 00:50:00 | 00:50:40 | 00:51:40 | 00:52:45 | 00:53:45 | 00:54:50 | 00:55:50 | 00:56:55 | 00:51:55 | 00:51:55 | 00:52:40 | 00:53:30 | 00:54:45 | 00:55:55 | 00:57:10 | 00:58:25 | 00:59:35 | 01:00:50 |
| 1 | 01:40:00 | 01:40:00 | 01:40:00 | 01:40:00 | 01:40:00 | 01:40:00 | 01:40:00 | 01:40:00 | 01:40:00 | 01:40:00 | 01:40:00 | 01:40:00 | 01:40:00 | 01:40:00 | 01:40:00 | 01:40:00 | 01:40:00 | 01:40:00 | 01:40:00 | 01:40:00 |
In Sheet2 column I2:j500
| Cat | HMS |
| SM | 00:29:39 |
| SM | 00:39:33 |
| 35-39M | 00:49:39 |
| SM | 00:50:52 |
| SM | 01:11:08 |
| SM | 01:31:17 |
I want to insert a formula that looks firstly at Sheet 2 Column I matches in to Sheet 1 and then looks at Sheet 2 Column J and allocates the correct points. In order to allocate point the HMS must be under the toime to achieve the point so for SM in order to get 5 points you need a time of under 00:36:00.
So for example:
| Cat | HMS | Point |
| SM | 00:29:39 | 5 |
| SM | 00:39:33 | 3 |
| 35-39M | 00:49:39 | 2 |
| SM | 00:50:52 | 1 |
| SM | 01:11:08 | 1 |
| SM | 01:31:17 | 1 |
