First & Last mentioned date in the column related to a matching cell in a row containing value

MaxNaprix

Hi,

I have a value in cell [Crew*]4 - "Max Test"

In cell [Start Date]4 I want to have a date from column Start40:Start50 related where "Max Test" was mentioned the first time in column [Crew*]40:[Crew*]50.

Similar in [Forecast Finish/ Completed*]4 I want to see the date from column [Forecast Finish/ Completed]40:[Forecast Finish/ Completed]50 where "Max Test" was mentioned last.

Please note that column Crew* can have multiple names, so it can not just match value from [Crew*]4, it would need to contain it.

I hope I explained it well enough to understand:)

Ryan Sides

Hi Max, you would use the MIN and COLLECT combo formula with HAS since you have multiple contacts in the same cell. Like:

=MIN(COLLECT(Start:Start, [Crew*]:[Crew*], HAS(@cell, "Max Test")))

Similar to the above for your forecast finish.

MaxNaprix

Thank you! It worked! I just needed to use MAX function for forecast finish

Ryan Sides

You’re welcome! Happy to help.