Returning the most recent value that matches multiple criteria (cross-sheet lookup)

Robert Bennett
Robert Bennett ✭✭
in Formulas and Functions

Hi All,

I am trying to create a preventative maintenance schedule for our fleet vehicles and heavy equipment machines based on the vehicle/machine, the most recent odometer readings or machine hours, and the type of service needed (PM Service 2, PM Service 3, Oil Change, etc.).

For my source sheet, I'm using a repair and maintenance sheet that covers all repairs and maintenance performed on all machines.

My desire is to only return the most recent odometer readings/machine hours of these services.

I tried VLOOKUP but can only search for one value and return 1 value.

How do I get (Can I get) SmartSheet to return a cross-sheet value that meets 2 or more criteria and can this work if there are multiple matches which differ only by when the entry was made?


Screen shot 1.png

Source Sheet:

Source snap shot.PNG


Thanks!

Tags:
· Share on FacebookShare on Twitter

Best Answer

Answers

  • Paul H
    Paul H ✭✭✭✭✭✭

    I will take a try at the first part of the problem.

    In your Equipment Repairs Log Sheet add a new checkbox column and use this as its column formula.

    =IF([Submission Date]@row = MAX(COLLECT([Submission Date]:[Submission Date], Machine:Machine, Machine@row)),1)

    This should checkmark the most recent submission for each machine.

    · Share on FacebookShare on Twitter
  • Robert Bennett
    Robert Bennett ✭✭
    Answer ✓

    Hey Paul,

    Your formula got me a step closer.

    Thanks!

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!