INDEX(COLLECT) function problem?

I am using an INDEX(COLLECT) function to return an amended date by family name. It works well except in the event a family was amended twice. It is returning the first amended value as opposed to the latest amended value. How can I get Smith to show up as Aug-27-2021 in Column 5? When I change row index to 2 it works for Williams but not for the others, so I am unsure how to "COLLECT" the latest revision. This is my Formula:

=IFERROR(IF(Entry@row = "Amended", "", INDEX(COLLECT([Revised Date]:[Revised Date], [Family Name]:[Family Name], [Family Name]@row, Entry:Entry, "Amended"), 1)), "")

Thanks in advance!



Best Answer


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hello @Susan Vieira

    MAX(COLLECT) should work for you

    =IFERROR(IF(Entry@row = "Amended", "", INDEX(MAX(COLLECT([Revised Date]:[Revised Date], [Family Name]:[Family Name], [Family Name]@row)), 1)), "")


  • Susan Vieira
    Susan Vieira ✭✭✭✭✭

    Perfect! I use INDEX/COLLECT so infrequently that I didn't even realize I could use MAX in there! Thank you very much.

  • Susan Vieira
    Susan Vieira ✭✭✭✭✭

    @Kelly Moore , I've encounter a hitch with this formula. It returns the latest date but if that date happens to be before the original, it will not. It returns the literal latest date. But I need it to return the date in the latest amendment, regardless if it is before or after the original.

    How can I leverage my formula to return the Revised date of the latest Amendment? Not necessarily the latest date? Does this make sense?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!