Index Match Formula help needed
I'm currently trying to calculate the fuel consumption for each truck by date by using using an Index Match formula as shown below
=INDEX(Odometer:Odometer, MATCH(MAX(COLLECT(Created:Created, Created:Created, @cell < Created@row, Truck:Truck, Truck@row)), Created:Created, 0), 0)
This formula is just the part where I am trying to get the odometer reading from the same truck the last time they filled up. However, as you will see in the screenshots it returns repeated values. So I changed the formula and added a column to see which trucks it was constantly repeating and as you will see in the screenshots, the error does lies with the trucks repeating themselves. It's constantly truck 14 and 16 repeating itself.
Could someone kindly help me fix this issue please?
Also for my formula could you help write a formula with the "Date" heading too? As whenever I try change the "Created" column to the "Date" column it says its "unparseable".
Help Article Resources
Check out the Formula Handbook template!