Issue with COLLECT and duplicate dates
We are trying to achieve a formula that finds the most current Tracked task's Variance from the Baseline date. Tasks are arranged in a table/schedule in step by step order.
I'm having an issue with this formula here:
=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, <>"")), [Task Complete Actual]5:[Task Complete Actual]128, 0))
It works as intended unless a non-Tracker task has the same Task Complete Actual date as the Tracked Task.
So instead of showing the Baseline Variance for the Tracked Task it shows an empty cell with no error message.
I'm thinking I need to either:
A. Add in an IF statement in the COLLECT function to filter the range more appropriately
B. Alter the Criteria/Criteria Range in the COLLECT function.
I've attempted to include a basic excel example as a visual aid, the rows that apply are the ones with 2/18/2019 in the Actual Complete column. Ideally, the yellow cell up top should read "4" but doesn't because Task 6 shares the same Actual Complete date. If the dates are different then it works as intended.