index match with multiple dates

Copper Contributor

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:

 

CommIDEmpIDEmpNameCategorySubjectViewed

Created

7.72E+082214626Doe, JonQuality & ServiceDSAT - 3/8 - Discussion - Call not ava - 1/16/201911/16/2019 21:59
7.63E+082214626Doe, JonGeneralDSAT - Disscussion - Verbal - Skill112/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!!

3 Replies

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

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