Sheet1
A B
1 logs
2 01/12/2011 00:00 =IF(AND(A2>=Sheet2!$B$2,A2<=Sheet2!$C$2),Sheet2!$A$2,"No Match")
3 01/12/2011 00:34
4 01/12/2011 08:11
5 01/12/2011 09:00
6 01/12/2011 10:06
7 01/12/2011 12:00
8 01/12/2011 12:32
9 01/12/2011 13:00
10 01/12/2011 14:23
11 01/12/2011 14:24
Sheet2
A B C
1 No start date time end date time
2 Event 1 01/12/2011 09:10 01/12/2011 17:15
3 Event 2 01/12/2011 11:45 01/12/2011 16:49
4 Event 3 08/12/2011 10:00 08/12/2011 16:00
5 Event 4 14/12/2011 06:45 14/12/2011 15:20
6 Event 5 21/12/2011 15:30 21/12/2011 20:30
7 Event 6 02/12/2011 11:00 02/12/2011 18:40
8 Event 7 10/12/2011 12:00 10/12/2011 22:00
9 Event 8 15/12/2011 20:30 15/12/2011 21:55
10 Event 9 16/12/2011 00:01 16/12/2011 00:01
11 Event 10 02/11/2011 12:30 02/11/2011 12:50
12 Event 11 02/12/2011 15:00 02/12/2011 18:45
13 Event 12 04/12/2011 14:45 04/12/2011 14:45
14 Event 13 05/12/2011 00:58 05/12/2011 01:25
15 Event 14 04/12/2011 16:30 04/12/2011 23:00
16 Event 15 06/12/2011 20:00 07/12/2011 09:00
This will do give the last event in the sheet2 list where the A2 value falls between two times:
B2: =IFERROR(LOOKUP(2, 1/((Sheet2!$B$2:$B$16<=$A2)*(Sheet2!$C$2:$C$16>=$A2)), Sheet2!$A$2:$A$16), "No Match")
...copied down.
Note: A7:A11 seem to have more than one match. The formula above will return the last match in the list.
Bookmarks