google sheets – Using VLOOKUP with pivot table – OR – another way to aggregate data

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).”