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

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

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    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

  • 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.
    Genevieve P. Employee Admin

    No problem! 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!