Index, Match, Max, Collect - Close , but off when same max dates (yet different criteria)

bloux

I am stumped and can't process how to adjust this formula when same dates are present in the dataset - I believe the collection is choosing the first return only-- rather than the field that should be collected based on the criteria. Match should be gathering the row - - but really unsure here.

=INDEX({Title}, MATCH(MAX(COLLECT({Date}, {Criteria 1}, 1, {Criteria 2}, 1)), {Date}, 0), 1)

Eg Dataset :

Same Max Date

2/21/2019 /Returned Index(title) /Criteria 1-Null /Criteria 2-Null

2/21/2019/Unreturned Index(title) / Criteria 2-Meets/Criteria 2-Meets

Genevieve P.

Hi @bloux

The MATCH Function works like this:

MATCH(Value to Match, Range to Search, 0)

This means that in the first part, the Value to Match, you're filtering by Criteria 1 and Criteria 2, so it finds 2/21/2019 as the Value.

However in the second part, the Range to Search, there are no other criteria listed to filter by, so it will find the first time that 2/21/2019 appears in the range. Does that make sense? We'll want to add in a COLLECT function in the Range portion of the formula, too:

=INDEX({Title}, MATCH(MAX(COLLECT({Date}, {Criteria 1}, 1, {Criteria 2}, 1)), COLLECT({Date}, {Criteria 1}, 1, {Criteria 2}, 1), 0), 1)

Cheers!

Genevieve