Index Match Formula help needed

Rainier Hollands

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?

screenshot 2.png

Snip it smartsheets.png

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".

Eric M Oliveira

Hi Rainier,

Happy to help, calculate the fuel consumption for each truck by date. From the description provided, I believe you may be able to achieve your desired goal utilizing a SUMIFS function instead given the values desired to calculate are numeric. Utilizing a SUMIFS function you can add the multiple desired criteria to pull the most recent date and sum all relative numeric values. 

SUMIFS: https://help.smartsheet.com/function/sumifs

If you need to visually display the rows text values for a specific date and need it to update as we move forward in time, based on your description, it may be simpler to utilize a Report to achieve. This Help Center article outlines in further detail Reports: https://help.smartsheet.com/articles/522214-creating-reports

If you are unable to produce the desired result after performing the above or if I have misunderstood the request, I think it would be best for us to set aside some time to have a screen share session to try to resolve the issue. Please connect with us utilizing this Support Form: https://help.smartsheet.com/contact

Please provide the best number you can be reached at and a Date/Time that would work best for you?

Note: Ensure to include your desired time zone. 

Have a wonderful day. Thank you for contacting Smartsheet Support.

Cheers,

Eric

Smartsheet Technical Support

Eric M Oliveira

Hi Rainier,

Happy to help, calculate the fuel consumption for each truck by date. From the description provided, I believe you may be able to achieve your desired goal utilizing a SUMIFS function instead given the values desired to calculate are numeric. Utilizing a SUMIFS function you can add the multiple desired criteria to pull the most recent date and sum all relative numeric values. 

SUMIFS: https://help.smartsheet.com/function/sumifs

If you need to visually display the rows text values for a specific date and need it to update as we move forward in time, based on your description, it may be simpler to utilize a Report to achieve. This Help Center article outlines in further detail Reports: https://help.smartsheet.com/articles/522214-creating-reports

If you are unable to produce the desired result after performing the above or if I have misunderstood the request, I think it would be best for us to set aside some time to have a screen share session to try to resolve the issue. Please connect with us utilizing this Support Form: https://help.smartsheet.com/contact

Please provide the best number you can be reached at and a Date/Time that would work best for you?

Note: Ensure to include your desired time zone. 

Have a wonderful day. Thank you for contacting Smartsheet Support.

Cheers,

Eric

Smartsheet Technical Support

Rainier Hollands

Hi Eric,

Thank you for your response. I was eventually able to figure it out. I appreciate your time and effort into writing a response! It was helpful!