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:)
Answers
-
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.
-
Thank you! It worked! I just needed to use MAX function for forecast finish
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!