Welcome to the New Smartsheet Online Community
You’ll notice that things have changed a bit. If you need help getting oriented, please take a look at the posts here in the Welcome to the Community category.
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