For each unique cell value in a given column, find the last occurrence of that value and if it meets a given criteria, then show it

Question

Imagine I have a Google spreadsheet that tracks whether or not a given guest is at a party. Each row in one sheet, Activity, notes a guest name and whether they have “arrived” or “departed.” Another sheet, Presence, should show who is currently at the party. How would I construct a formula to check whether or not an activity row exists that says “departed” for every guest who has “arrived”? I’ve been thinking about trying to use MAXIFS, or perhaps FILTER, but I can’t figure out how to construct the formula to do what I need.

Activity table

Time	Person	Action
7:01pm	John	Arrived
8:03pm	John	Departed
8:17pm	Suzy	Arrived
9:12pm	John	Arrived

Presence table (assume time is now 9:30pm)

Person	Present now
John	Yes
Suzy	Yes

Answer 1

If you don't need to compare times, but just to find last value, you can use XLOOKUP:

=IF(XLOOKUP(E2,B:B,C:C,,,-1)="Arrived","Yes","No")

As an array, so it "expands" to the whole column:

=ARRAYFORMULA(IF(E2:E="","",IF(XLOOKUP(E2:E,B:B,C:C,,,-1)="Arrived","Yes","No")))

And, if you want to grab also the names, and generate the whole table expanding with new names:

=LET(names,TOCOL(UNIQUE(B2:B),1),
BYROW(names,LAMBDA(name,
HSTACK(name,IF(XLOOKUP(name,B:B,C:C,,,-1)="Arrived","Yes","No")))))

Answer 2

I figured it out using a third table, People, along with the FILTER, SUMPRODUCT, and INDEX functions. Posting in case this helps someone learn in the future. My People table looks like this:

Person	Status
John	(function below)
Suzy	(function)
Ross	(function)

Function:

=IFERROR(INDEX(FILTER(Activity!$C:$C, Activity!$B:$B=A2),SUMPRODUCT(Activity!$B:$B=A2)),"")

Which basically says "Use the Filter function to look in the Activity table for the value (Arrived or Departed) corresponding to the name of a person in the same row which equals the name of this person here in this table. Then create an index of the times that person's name occurs in the Activity table (INDEX) and grab the last entry." I construct the "last entry" by saying "get the sum of entries in Activity who name matches this name and use that for the second value in the Index function." The IFERROR part just says "leave the cell blank if that person has neither arrived nor departed".

On top of all that, I then made a filter function on the Presence table...

=FILTER(People!$A:$A,People!$B:$B="Arrived")

...which basically says "filter results from the People table for those people whose status is 'Arrived'". I got help from this video. I think @martin's answer is probably more future proof and robust, but this answer also works.