Forum Discussion
Jbowyer86
Jan 19, 2019Copper Contributor
index match with multiple dates
I have a workbook built for my company that uses index match to return the subject line of a employee communication. However if there are multiple communications the formula only returns the first entry. What can I use to return the subject line based on the most recent date? There is an additional column that contains the date that the communication was sent.
This is the data I am using:
CommID | EmpID | EmpName | Category | Subject | Viewed | Created |
7.72E+08 | 2214626 | Doe, Jon | Quality & Service | DSAT - 3/8 - Discussion - Call not ava - 1/16/2019 | 1 | 1/16/2019 21:59 |
7.63E+08 | 2214626 | Doe, Jon | General | DSAT - Disscussion - Verbal - Skill | 1 | 12/25/2018 22:33 |
I want to return the Subject based on the most recent entry in Created.
I am currently using this formula to pull the data:
=IFERROR(INDEX(KPICommDetail[Subject],MATCH([Ident],KPICommDetail[EmpID],0)),"")
Help and thanks!!
Hi,
That could be
=IFERROR(INDEX(Table2[subject],AGGREGATE(14,6,1/(Table2[empid]=[@empid])*( ROW(Table2[empid]) - ROW(Table2[[#Headers],[empid]])),1)),"")
or, if you are on Office365
=IFERROR(INDEX(Table2[subject],MATCH(1,INDEX((Table2[empid]=[@empid])*(Table2[date]=MAXIFS(Table2[date],Table2[empid],[@empid])),0),0)),"")
Both are in attached file
- Jbowyer86Copper Contributor
Thanks but I couldn't get that to work. I updated the original post with more information if that helps.
If your source table is sorted on date your formula works
=IFERROR(INDEX(KPICommDetail[Subject],MATCH([@Ident],KPICommDetail[EmpID],0)),"")
If not sorted, then
=IFERROR(INDEX(KPICommDetail[Subject],MATCH(AGGREGATE(14,6,1/([@Ident]=KPICommDetail[EmpID])*KPICommDetail[Created],1),KPICommDetail[Created],0)),"")
Attached