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

bloux

Wow yes, thank you for thinking that one through! Was not aware that the formulas could be used to filter the range like that also! Thanks so much Genevieve!

Genevieve P.

No problem! 🙂