Get newest date from a matching row in a separate sheet.

RicqP

I have 2 sheets: Member Roster and Annual Member Survey. A member will only be listed once in Member Roster but may have one or more rows in Annual Member Survey. The field Email is used to connect each sheet to the other.

I have set up 2 cross-sheet references from Member Roster to Annual Member Survey: 1) "Annual Member Survey | Created" (this is the date the survey is submitted) and 2) "Annual Member Survey | Email".

This is my current column formula in Member Roster/Last Completed Member Survey:

=MAX(
COLLECT(
(INDEX({Annual Member Survey | Created}, MATCH(Email@row, {Annual Member Survey | Email}, 0))),
(INDEX({Annual Member Survey | Created}, MATCH(Email@row, {Annual Member Survey | Email}, 0))),
<>"")
)

This formula doesn't return the newest Created date back to Member Roster. Example: for the email value aaa@bbb.com the formula should return 09/12/24 but it's returning 08/08/24. Why?

Adam Murphy

I think something like this should work:

=MAX(COLLECT({Annual Member Survey | Created}, {Annual Member Survey | Email}, Email@row))

RicqP

Adam, that did it, thank you so much! I always overthink & over-complicate my formulas.

Adam Murphy

I think something like this should work:

=MAX(COLLECT({Annual Member Survey | Created}, {Annual Member Survey | Email}, Email@row))

RicqP

Adam, that did it, thank you so much! I always overthink & over-complicate my formulas.