Index match a time to +/- 3 seconds (If it's possible that is)

[BigErn]

Hi All,
Hi time poster (because I'm stuck!!). I've two tables both have a number reference and a start time. I'm trying to index a text column in one table, match the number and start time and show the text in the other table. Not a problem I've used to following formula.

{=INDEX($F$18:$F$32,MATCH(1,($C2=$A$18:$A$32)*($I2=$I$18:$I$32),0))}

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Station[/TD]
[TD]Facility[/TD]
[TD]Word3[/TD]
[TD]Word22[/TD]
[TD]Word5[/TD]
[TD]FaultDesc[/TD]
[TD]RsnCode[/TD]
[TD]RsnDesc[/TD]
[TD]StartTime[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]9131[/TD]
[TD]MS01[/TD]
[TD]9117[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]=Aboveformula[/TD]
[TD]101[/TD]
[TD]Step 1 interlock[/TD]
[TD]25/06/2018 11:46:36[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 500"]
<tbody>[TR]
[/TR]
[TR]
[TD]
17[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Station[/TD]
[TD]Facility[/TD]
[TD]Word3[/TD]
[TD]Word22[/TD]
[TD]Word5[/TD]
[TD]FaultDesc[/TD]
[TD]RsnCode[/TD]
[TD]RsnDesc[/TD]
[TD]StartTime[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]9117[/TD]
[TD]OP30[/TD]
[TD]0[/TD]
[TD]9[/TD]
[TD]3076[/TD]
[TD]10:._025S521 UN104 Part Present[/TD]
[TD]101[/TD]
[TD]Step 1 interlock[/TD]
[TD]25/06/2018 11:46:36[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


My problem is that the Start time doesn't always exact match, it can be +/-3 seconds out which is a real pain!! Can anybody advise me how to do this?

 

[BiocideJ]

I BELIEVE this should work. You just need to be a little less specific in your match and allow for a +/i by looking >= t-3 and <= t+3 like so

{=INDEX($F$18:$F$32,MATCH(1,($C2=$A$18:$A$32)*($I2-TIMEVALUE("0:00:03")<=$I$18:$I$32)*($I2+TIMEVALUE("0:00:03")>=$I$18:$I$32),0))}

edit: same functionality, but using TIME is perhaps a little shorter (if slightly more obsure as to what it is doing)

{=INDEX($F$18:$F$32,MATCH(1,($C2=$A$18:$A$32)*($I2-TIME(0,0,3)<=$I$18:$I$32)*($I2+TIME(0,0,3)>=$I$18:$I$32),0))}

 

[Legacy 421546]

Internally, Excel is representing dates as floating point numbers. The integer part is the date and the fractional part is the time. With this in mind you can compare the time as floating point numbers which might be easier.

 

[BigErn]

I BELIEVE this should work. You just need to be a little less specific in your match and allow for a +/i by looking >= t-3 and <= t+3 like so

{=INDEX($F$18:$F$32,MATCH(1,($C2=$A$18:$A$32)*($I2-TIMEVALUE("0:00:03")<=$I$18:$I$32)*($I2+TIMEVALUE("0:00:03")>=$I$18:$I$32),0))}

edit: same functionality, but using TIME is perhaps a little shorter (if slightly more obsure as to what it is doing)

{=INDEX($F$18:$F$32,MATCH(1,($C2=$A$18:$A$32)*($I2-TIME(0,0,3)<=$I$18:$I$32)*($I2+TIME(0,0,3)>=$I$18:$I$32),0))}

Sir,
You are a gentleman and a scholar! Works brilliantly many thanks.