2

My nested COLLECT function's Criteria does not appear to be working because the formula runs into errors when a duplicate date is found before the actual date that should be being pulled. 

 

=INDEX([Baseline Variance]5:[Baseline Variance]128, MATCH(MAX(COLLECT([Task Complete Actual]5:[Task Complete Actual]128, [Milestone Type Tracker]5:[Milestone Type Tracker]128, ="Tracker")), [Task Complete Actual]5:[Task Complete Actual]128, 0))

 

The [Milestone Type Tracker]5:[Milestone Type Tracker]128, ="Tracker" should filter out the row that has an identical Task Complete Actual date because it does not have the "Tracker" criteria.

 

Excel example attached

Yellow cell should read "4" but doesn't because the duplicate dates in Actual Complete column for Tasks 6 & 7

Comments

My current thinking is that the MATCH(MAX(COLLECT())) is returning the proper date but the Index isn't following the criteria I have in COLLECT. I suppose I could include an IF statement in INDEX to filter on Tracked tasks but I'm unsure of how exactly I would do that.

What type of column is your [Milestone Type Tracker]? Can you provide a screenshot of your SS version?