Combining a IF function and lookup function

Attachment 330782Attachment 330788Attachment 330790HI

I don't know if is the best way of doing this and even if this will work!. I am trying to match up the data from a bat detector to a collection of video files. The video camera creates a new file ever 30:00 mins or ever 1 hour depending on the quality setting.

So firstly,I am wanting to determine the video file name i need to watch using the time and date of the bat pass against the start time/date and end time/date of the video i.e. if it falls in between them. Then from this I want to determine the run time on the video, so i can just watch the bit of the video containing the bat.

I have the video information in one sheet and the bat data in the other. I thought i could use a if statement combined with a lookup to return the video file name. And then determine the video run time from the time of the bat and video start time.

some like this =IF(AND(H3>=Sheet2!Z:Z,H3<=Sheet2!AA:AA),LOOKUP(H3,video!X:X,video!J:J),0)

it not working though, anyone got any ideas please









Attachment 330790

Q3: =LOOKUP(H3,video!X:X,video!J:J)
R3: =INDEX(Sheet2!Q:Q, MATCH($Q3, Sheet2!$L:$L, 0))
S3: =(INDEX(Sheet2!P:P, MATCH($Q3, Sheet2!$L:$L, 0))-INDEX(Sheet2!O:O, MATCH($Q3, Sheet2!$L:$L, 0)))*24 (formatted as General)

Those formulas all error in row 3, but when you copy down the rest resolve correctly.