Using VLOOKUP with pivot table - OR - another way to aggregate data

Question

My sample is here:

https://docs.google.com/spreadsheets/d/1j9pRECnAKtKtvAfhMvLv93-u6_kwIrFZg3RY3jSkpkU/edit?usp=sharing

I am trying to aggregate the last date that each "familyID" was contacted, based on the max "lastContact" date, between rows that have the same familyID but different personIDs. In my sample, every row in the REALlastContact column should say 4/28/21. I got so far as to aggregate the data in a pivot table that returns the correct last contact date for each familyID... but I need that information to be part of the same spreadsheet. In my real data, my pivot table would have to be on a separate page and cross-referencing is what I am trying to avoid. I am trying to use VLOOKUP referencing columns of that pivot table, which requires turning on iterative calculation, to avoid a circular dependency (the source of which I am not completely aware of, but sure), it seems to work but only in one cell at a time. I am very confused by this! What is the difference between C2 and C3?

Any advice would be appreciated!

Answer 1

You don't need the pivot table at all (i.e., you can delete it altogether).

Delete everything in Column C (including the header) and place the following formula in C1:

=ArrayFormula({"REALlastContact";IF(B2:B="",,VLOOKUP(B2:B,SORT({B2:B,D2:D},2,0),2,FALSE))})

This reads, in plain English, as follows: "Process an entire array, not just one cell. First, put the header. Under that (as indicated by the semicolon), if any row is blank in Column B, leave it null in Column C. Otherwise, look up whatever is in that row of Column B within a two-column array of the family paired with the contact date, sorted upside-down by contact date, and return the contact date (which will be the most recent, because the highest/most recent dates will be found first when sorted upside-down)."