Collect Criteria not being filtered
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
Help Article Resources
Check out the Formula Handbook template!