Returning the most recent entry in Excel

Question

My workbook has two sheets, we will call them Summery and Events.

In my Summery sheet, it looks like this

Name	Last Event
Name 1	Event 3
Name 2	Event 15

In my Events sheet it looks like this

Name	Event	Date
Name 1	Event 1	4/01/2022
Name 1	Event 2	4/01/2022
Name 1	Event 3	4/02/2022
Name 1	Event 4	4/02/2022
Name 2	Event 1	4/03/2022
Name 2	Event 5	4/03/2022
Name 2	Event 10	4/04/2022
Name 2	Event 11	4/10/2022
Name 2	Event 15	4/29/2022
Name 2	Event 16	4/29/2022

On the Summery sheet I am using a FILTER to return the name, event, and date from the Events sheet on the condition that the name is equal to it's counterpart in row A. And then I use a SORT to order the names events and date by the date descending to get the row with the most recent date. Then I get an INDEX of the topmost row and return the second column... the problem is. Some events will occur on the same day so I won't get the most recent event. I will get the first event on the most recent day.

My formula on the summery sheet in B2 looks something like this.

=INDEX(SORT(FILTER(Events!A2:C, Events!A2:A = A2),3,FALSE),1,2)

How would I go about returning the most recent event? I've tried this.

=INDEX(SORT(FILTER({Events!A2:C, ROW(Events!A2:A)}, Events!A2:A = A2),3,FALSE,4,FALSE),1,2)

And it works fine in google sheets. But it doesn't work in Excel. How would I get this solution in excel

Thanks

Answer 1

To get the last entry with the desired name:

=INDEX(FILTER(B2:B11,A2:A11=F1),COUNTIF(A:A,F1))

---

If indeed dates are always in order one could use:

=LOOKUP(2,1/(A2:A11=F1),B2:B11)

Or the ms365's equivalent:

=XLOOKUP(F1,A2:A11,B2:B11,,,-1)