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

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:)

Find more posts tagged with

Accepted answers

All comments

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.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.