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

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?

Source Sheet:

Thanks!

Find more posts tagged with

Construction

Formulas

Cell linking

Accepted answers

Robert Bennett

Hey Paul,

Your formula got me a step closer.

Thanks!

All comments

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.

Robert Bennett

Hey Paul,

Your formula got me a step closer.

Thanks!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.