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))}

	A	B	C	D	E	F	G	H	I
1	Station	Facility	Word3	Word22	Word5	FaultDesc	RsnCode	RsnDesc	StartTime
2	9131	MS01	9117	0	1	=Aboveformula	101	Step 1 interlock	25/06/2018 11:46:36
3
4
etc

<tbody>
</tbody>

17	A	B	C	D	E	F	G	H
18	Station	Facility	Word3	Word22	Word5	FaultDesc	RsnCode	RsnDesc	StartTime
19	9117	OP30	0	9	3076	10:._025S521 UN104 Part Present	101	Step 1 interlock	25/06/2018 11:46:36
20
21
etc

<tbody>
</tbody>

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.