Lookup values based on a date and a timespan

[npromsiri]

Hey all, would really appreciate any help you could give me.

Table 1


I would like to lookup the date in column C to see if it matches the date in column B in the table below (Table 2), and then check if the time within the same row is within +/- 5 minutes of the time in Table 2's column C. If the two conditions are met, then I would like to lookup the value for columns B, E and F in Table 1 to put them respectively into columns D, E and F in Table 2.

Table 2



My logical tests for comparing times do not seem to be working (e.g. 3:05 AM > 3:00 AM is recording a False).

Thanks.

 

[estevaoba]

Try this in Table 2 D2 and copy down:

[TABLE="width: 64"]
<tbody>[TR]
  [TD="width: 64"]=IF(AND(B1='Table  1'!C1,AND('Table 1'!D1>=C1-5/60/24,'Table  1'!D1<=C1+5/60/24)),INDEX('Table 1'!$A$1:$F$6,MATCH(B1,'Table  1'!$C$1:$C$6,0),2),"")[/TD]
[/TR]
</tbody>[/TABLE]

For the other columns, change the column number in the formula accordingly.

Godspeed!

 

[estevaoba]

Ref my formula above, this one is neater:

=[TABLE="width: 64"]
<tbody>[TR]
  [TD="width: 64"]=IF(AND(B1='Table  1'!C1,'Table 1'!D1>=C1-5/60/24,'Table 1'!D1<=C1+5/60/24),INDEX('Table  1'!$A$1:$F$6,MATCH(B1,'Table 1'!$C$1:$C$6,0),2),"")[/TD]
[/TR]
</tbody>[/TABLE]

 

[npromsiri]

Hey estevaoba, thanks for the help. However i forgot to mention that the date columns in the two tables do not match up perfectly (i.e. there are more dates in table 1 than table 2). So the dates referenced in table 2 quickly fall behind. Is there anyway to work around this?