INDEX(COLLECT) function problem?

Susan Vieira

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!

Susan

Kelly Moore

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)), "")

cheers

Kelly Moore

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)), "")

cheers

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

@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?