if false evaluate next row

[cabaret]

Hi Guys, Hopefully someone out there can help me.

I have a list of date & times in sheet 1 column A. For each of those times I need to know if they occurred within any of the time frames on sheet 2, with start date & time in column B and end date & time in column C. Column A has the event numbers.

I have an if formula in sheet 1 B2 that says, if date and time in sheet 1 A2 is greater than or equal to start date and time in sheet 2 B2 and greater than or equal to end date and time in sheet 2 C2 then return event number in sheet 2 A2.

However, if it is not then I want it to check the same date and time in sheet 1 A2 against the time frames in sheet 2 B3 and sheet 2 C3 and continue to work downwards until an event number is returned or there is no more data.

I found some examples on various forums but they were always posted in a newer version of excel and I couldn't read them properly in excel 2003

I tried to upload a small example data set but for some reason it wouldn't work.

Thanks for any help

[JBeaucaire]

You can zip up your workbook if needed, then it should upload no problem.

[cabaret]

Sheet2

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

Still couldn't upload so here is a typed out dummy data set

For the formula shown I don't want to show no match if it is false. I want to check the same value (A2) against sheet2 B3 & c£ and then B4 & C4 until there is a match.

Thanks

[cabaret]

OK that's not how it looked when I typed it. Hopefully you get the idea though??

[JBeaucaire]

Please Login or Register  to view this content.

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.

[cabaret]

As that formula is beyond me I just copied it out into the task bar and hoped it would work. The result was #NAME?, which again I don't recognise. I've had VALUE before but not #NAME?. Do you know what I did wrong?

[JBeaucaire]

No, but I'll look at your work,certainly. Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

[NBVC]

Could be that IFERROR is not available in 2003....

[JBeaucaire]

Well of course! Sorry about that. Here's the long ugly version for Excel 2003:

=IF(ISERROR(LOOKUP(2, 1/((Sheet2!$B$2:$B$16<=$A2)*(Sheet2!$C$2:$C$16>=$A2)), Sheet2!$A$2:$A$16)), "No Match", LOOKUP(2, 1/((Sheet2!$B$2:$B$16<=$A2)*(Sheet2!$C$2:$C$16>=$A2)), Sheet2!$A$2:$A$16))

[cabaret]

That's done it. Thank you very much for your help. With over 13000 logs and 591 time frames to examine you really have solved a huge problem for me.

Thank you