VLOOKUP record with latest date

Solution 1:

If you have a newer version of excel use =XLOOKUP()

your formula then becomes: =XLOOKUP(A2,Log!$A$1:$A$8,Log!$G$1:$G$8,"",0,-1)

one of the things the xlookup does is combines the iferror and vlookup into 1 function. The other thing that it does is it can search backwards. So as long as your log is in chronological order you can use the -1 as the last item and it will search backwards (of course you could continue with your formula and just sort your table array with newest on top...)

Solution 2:

If you don't mind adding a helper column, then here's one way. In your LOG sheet, add another column. Formula for column I (Max_Contact_Flag)...

=IF(MAXIFS(C:C,A:A,[@Name])=[@[Date Contacted]],"Yes","No")

This flag is looking up the most recent contact date per person, and if the max date matches the current row's date for that person, then it's returning a YES value.

If you're not using a table, then replace @Variables with column ranges. But I highly recommend using a table because the helper column will automatically recalculate when adding a new row, so no need to keep dragging formulas.

Then, you could either filter on the YES values in your table, or, you could also create a pivot table on your data. In the pivot, then set your filter to Yes. This pivot would represent your OVERVIEW.