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

Best Answer

  • Eric M Oliveira
    Eric M Oliveira Employee
    Answer ✓

    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

Answers

  • Eric M Oliveira
    Eric M Oliveira Employee
    Answer ✓

    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

  • 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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!