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
CodePudding user response：
To get the last entry with the desired name:
=INDEX(FILTER(B2:B11,A2:A11=F1),COUNTIF(A:A,F1))
