Index & Match with multiple criteria (match time prior to second column time value)

[Botanic123]

Hi Folks,

Would really appreciate if someone was able to help me as my brain has turned to mush!

Attached is file showing miles from a tracker odometer reading (column C) that I need to be able to match to a employee fuel stop (enter into column I). The tracker date/time (column B) is generated from ignition off periods from the employee vehicle. The fuel stop date/time (column G) is generated from using fuel card at station. There are approx 5 minute difference between the two times registered in column B and column G. I think I need index and match with multiple criteria here? I need to match driver name (column E) with vehicle (column A), then match the time immediately prior (in column B) to the time in column G to return the odometer value for that employee at that specific date and time.

I managed a formula to get me the time immediately prior but it is pulling it from the whole column, not specific to an employee. When I try to enter other match criteria in I just get #ref or #name.

Example of return for I3 would be 78,147 (coming from C23).

Any help would be great

Tracker 1.xlsx
	A	B	C	D	E	F	G	H	I
1					FUEL CARD
2	VEHICLE	DATE /TIME	ODOMETER		DRIVER NAME	MILEAGE	DATE /TIME	CLOSEST TIME PRIOR TO COLUMN G	ODOMETER
3	Employee 3	16/08/2021 14:53:57	76,431		Employee 1	84,367	23/08/2021 07:12:00	23/08/2021 07:09:24
4	Employee 3	16/08/2021 15:06:04	76,436		Employee 2	90,201	17/08/2021 09:56:00	17/08/2021 09:54:38
5	Employee 3	16/08/2021 15:50:06	76,440		Employee 3	96,169	16/08/2021 17:07:00	16/08/2021 17:05:37
6	Employee 3	16/08/2021 17:05:37	76,489		Employee 3	97,547	29/08/2021 19:37:00	29/08/2021 19:35:10
7	Employee 3	16/08/2021 17:12:55	76,490
8	Employee 3	16/08/2021 18:48:10	76,492
9	Employee 3	17/08/2021 07:35:02	76,551
10	Employee 3	29/08/2021 15:14:28	77,863
11	Employee 3	29/08/2021 15:17:37	77,863
12	Employee 3	29/08/2021 19:31:37	77,863
13	Employee 3	29/08/2021 19:35:10	77,864
14	Employee 3	29/08/2021 19:42:58	77,865
15	Employee 3	30/08/2021 06:31:15	77,866
16	Employee 3	30/08/2021 07:40:05	77,924
17	Employee 3	30/08/2021 07:50:49	77,924
18	Employee 1	21/08/2021 10:34:28	78,080
19	Employee 1	21/08/2021 11:21:00	78,080
20	Employee 1	21/08/2021 11:33:35	78,081
21	Employee 1	21/08/2021 12:18:51	78,112
22	Employee 1	21/08/2021 12:27:57	78,113
23	Employee 1	23/08/2021 07:09:24	78,147
24	Employee 1	23/08/2021 07:30:13	78,158
25	Employee 1	23/08/2021 07:53:54	78,159
26	Employee 1	23/08/2021 09:50:57	78,159
27	Employee 1	23/08/2021 09:54:20	78,159
28	Employee 1	23/08/2021 09:59:23	78,159
29	Employee 1	23/08/2021 11:08:39	78,159
30	Employee 1	23/08/2021 12:30:21	78,159
31	Employee 1	23/08/2021 12:54:22	78,159
32	Employee 1	23/08/2021 13:29:45	78,159
33	Employee 1	23/08/2021 17:18:54	78,201
34	Employee 2	16/08/2021 07:30:40	69,896
35	Employee 2	16/08/2021 07:32:50	69,896
36	Employee 2	16/08/2021 07:35:31	69,896
37	Employee 2	16/08/2021 08:42:27	69,896
38	Employee 2	16/08/2021 08:47:12	69,896
39	Employee 2	16/08/2021 08:52:39	69,896
40	Employee 2	16/08/2021 09:10:20	69,898
41	Employee 2	16/08/2021 09:24:01	69,900
42	Employee 2	16/08/2021 09:57:36	69,900
43	Employee 2	16/08/2021 10:28:34	69,900
44	Employee 2	16/08/2021 10:30:22	69,900
45	Employee 2	16/08/2021 10:35:33	69,900
46	Employee 2	16/08/2021 11:15:53	69,913
47	Employee 2	16/08/2021 11:18:21	69,913
48	Employee 2	16/08/2021 11:18:28	69,913
49	Employee 2	16/08/2021 15:06:02	69,918
50	Employee 2	16/08/2021 16:20:56	69,938
51	Employee 2	16/08/2021 16:35:47	69,943
52	Employee 2	16/08/2021 16:43:03	69,944
53	Employee 2	17/08/2021 07:41:34	69,970
54	Employee 2	17/08/2021 08:02:00	69,970
55	Employee 2	17/08/2021 08:25:12	69,970
56	Employee 2	17/08/2021 08:40:17	69,970
57	Employee 2	17/08/2021 09:00:33	69,970
58	Employee 2	17/08/2021 09:03:12	69,971
59	Employee 2	17/08/2021 09:21:09	69,971
60	Employee 2	17/08/2021 09:25:33	69,971
61	Employee 2	17/08/2021 09:37:12	69,973
62	Employee 2	17/08/2021 09:37:53	69,973
63	Employee 2	17/08/2021 09:54:38	69,973
64	Employee 2	17/08/2021 10:04:40	69,975
65	Employee 2	17/08/2021 10:36:01	69,977
66	Employee 2	17/08/2021 10:40:42	69,977
67	Employee 2	17/08/2021 11:24:42	69,995
68	Employee 2	17/08/2021 13:10:43	70,002
69	Employee 2	17/08/2021 13:32:00	70,002
70	Employee 2	17/08/2021 14:06:38	70,008
71	Employee 2	17/08/2021 15:08:52	70,008
72	Employee 2	17/08/2021 16:24:55	70,037
73	Employee 2	17/08/2021 16:33:14	70,038
Sheet1

Cell Formulas
Range	Formula
H3:H6	H3	=INDEX($B$3:$B$19532,MATCH(TRUE,INDEX(MIN(INDEX(ABS($B$3:$B$1532-$G3),0))=INDEX(ABS($B$3:$B$1532-$G3),0),0),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

How about

=MAX(FILTER($C$3:$C$100,($A$3:$A$100=E3)*($B$3:$B$100<=G3)))

 

[Botanic123]

Yes, that's perfect! Thanks very much for your help

 

[Fluff]

You're welcome & thanks for the feedback.