Forum Discussion

Jbowyer86's avatar
Jbowyer86
Copper Contributor
Jan 19, 2019

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 en...
excel
INDEX MATCH
SergeiBaklan's avatar
SergeiBaklan
MVP
Jan 19, 2019

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

Jbowyer86's avatar
Jbowyer86
Copper Contributor
Jan 20, 2019

Thanks but I couldn't get that to work. I updated the original post with more information if that helps.

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Jan 20, 2019

    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

Resources