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!

Susan

Find more posts tagged with

Formulas

Accepted answers

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

All comments

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?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.