Index Collect formula?

Michael Company

I am trying to collect information from a combination of 3 sheets.

Job Duties is a multi select on employee Database

Crew is a multi select on job log

Vehicle is multi select on job log

Driver on asset summary sheet would pull in Driver(s) from the job log based on the vehicle.

On the Asset Summary Sheet, I’m trying to get the most recent possible drivers from the employee database based on a job log that was submitted. Do you know how to do this?

Image Attached.

Devin Lee

The first step you can use this formula in the Asset Summary Sheet and reference to the Job Log

=INDEX({Ref 1: Crew:Crew}, MATCH(MAX(COLLECT({Ref 2: Date:Date}, {Ref 3: Vehicle:Vehicle}, HAS(@cell, [Vehicle]@row))), Date:Date, 0), 0)

This will pull in the crew information for the latest date that the vehicle was used. You might have issues here if you have multiple uses in one day but that isn't the case in your example so I'm skipping that part which is doable but requires more steps.

After you have that information on the Asset Summary Sheet you need to check the list for drivers which is the more manual part because there isn't a good way to cross reference two lists against one another when one comes in as a single cell without creating circular references. There is a simple and complicated way to do this step but from experience simple is better even if it means more work down the road to keep it updated. You will use an IF statement for each driver's name but I prefer separating them out into multiple columns for each persons name and then joining them together rather than having a long formula. It's easier to add and remove columns than it is to go into a complicated formula and find the right place to modify it. For this example you would have column Jake and column Danny with the corresponding formulas

=IF(CONTAINS("Jake", Driver@row) = 1, "Jake", "")

=IF(CONTAINS("Danny", Driver@row) = 1, "Danny", "")

After you have all the columns you can add one more column which will join all the names together so you can hide the individual name columns. In the Join formula you want to have it across all the name. In this example you only have two but if there was more you would want to have them all.

=JOIN(Jake@row:Danny@row, ", ")