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

Botanic123

New Member
Joined
Sep 13, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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
ABCDEFGHI
1FUEL CARD
2VEHICLEDATE /TIME ODOMETER DRIVER NAME MILEAGE DATE /TIMECLOSEST TIME PRIOR TO COLUMN GODOMETER
3Employee 316/08/2021 14:53:5776,431Employee 184,36723/08/2021 07:12:0023/08/2021 07:09:24
4Employee 316/08/2021 15:06:0476,436Employee 290,20117/08/2021 09:56:0017/08/2021 09:54:38
5Employee 316/08/2021 15:50:0676,440Employee 396,16916/08/2021 17:07:0016/08/2021 17:05:37
6Employee 316/08/2021 17:05:3776,489Employee 397,54729/08/2021 19:37:0029/08/2021 19:35:10
7Employee 316/08/2021 17:12:5576,490
8Employee 316/08/2021 18:48:1076,492
9Employee 317/08/2021 07:35:0276,551
10Employee 329/08/2021 15:14:2877,863
11Employee 329/08/2021 15:17:3777,863
12Employee 329/08/2021 19:31:3777,863
13Employee 329/08/2021 19:35:1077,864
14Employee 329/08/2021 19:42:5877,865
15Employee 330/08/2021 06:31:1577,866
16Employee 330/08/2021 07:40:0577,924
17Employee 330/08/2021 07:50:4977,924
18Employee 121/08/2021 10:34:2878,080
19Employee 121/08/2021 11:21:0078,080
20Employee 121/08/2021 11:33:3578,081
21Employee 121/08/2021 12:18:5178,112
22Employee 121/08/2021 12:27:5778,113
23Employee 123/08/2021 07:09:2478,147
24Employee 123/08/2021 07:30:1378,158
25Employee 123/08/2021 07:53:5478,159
26Employee 123/08/2021 09:50:5778,159
27Employee 123/08/2021 09:54:2078,159
28Employee 123/08/2021 09:59:2378,159
29Employee 123/08/2021 11:08:3978,159
30Employee 123/08/2021 12:30:2178,159
31Employee 123/08/2021 12:54:2278,159
32Employee 123/08/2021 13:29:4578,159
33Employee 123/08/2021 17:18:5478,201
34Employee 216/08/2021 07:30:4069,896
35Employee 216/08/2021 07:32:5069,896
36Employee 216/08/2021 07:35:3169,896
37Employee 216/08/2021 08:42:2769,896
38Employee 216/08/2021 08:47:1269,896
39Employee 216/08/2021 08:52:3969,896
40Employee 216/08/2021 09:10:2069,898
41Employee 216/08/2021 09:24:0169,900
42Employee 216/08/2021 09:57:3669,900
43Employee 216/08/2021 10:28:3469,900
44Employee 216/08/2021 10:30:2269,900
45Employee 216/08/2021 10:35:3369,900
46Employee 216/08/2021 11:15:5369,913
47Employee 216/08/2021 11:18:2169,913
48Employee 216/08/2021 11:18:2869,913
49Employee 216/08/2021 15:06:0269,918
50Employee 216/08/2021 16:20:5669,938
51Employee 216/08/2021 16:35:4769,943
52Employee 216/08/2021 16:43:0369,944
53Employee 217/08/2021 07:41:3469,970
54Employee 217/08/2021 08:02:0069,970
55Employee 217/08/2021 08:25:1269,970
56Employee 217/08/2021 08:40:1769,970
57Employee 217/08/2021 09:00:3369,970
58Employee 217/08/2021 09:03:1269,971
59Employee 217/08/2021 09:21:0969,971
60Employee 217/08/2021 09:25:3369,971
61Employee 217/08/2021 09:37:1269,973
62Employee 217/08/2021 09:37:5369,973
63Employee 217/08/2021 09:54:3869,973
64Employee 217/08/2021 10:04:4069,975
65Employee 217/08/2021 10:36:0169,977
66Employee 217/08/2021 10:40:4269,977
67Employee 217/08/2021 11:24:4269,995
68Employee 217/08/2021 13:10:4370,002
69Employee 217/08/2021 13:32:0070,002
70Employee 217/08/2021 14:06:3870,008
71Employee 217/08/2021 15:08:5270,008
72Employee 217/08/2021 16:24:5570,037
73Employee 217/08/2021 16:33:1470,038
Sheet1
Cell Formulas
RangeFormula
H3:H6H3=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.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,041
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=MAX(FILTER($C$3:$C$100,($A$3:$A$100=E3)*($B$3:$B$100<=G3)))
 
Solution

Botanic123

New Member
Joined
Sep 13, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Yes, that's perfect! Thanks very much for your help:giggle:
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,041
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,148,333
Messages
5,746,145
Members
423,994
Latest member
blzxatly

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top