Home

index match with multiple dates

%3CLINGO-SUB%20id%3D%22lingo-sub-321202%22%20slang%3D%22en-US%22%3Eindex%20match%20with%20multiple%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-321202%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20workbook%20built%20for%20my%20company%20that%20uses%20index%20match%20to%20return%20the%20subject%20line%20of%20a%20employee%20communication.%20However%20if%20there%20are%20multiple%20communications%20the%20formula%20only%20returns%20the%20first%20entry.%20What%20can%20I%20use%20to%20return%20the%20subject%20line%20based%20on%20the%20most%20recent%20date%3F%20There%20is%20an%20additional%20column%20that%20contains%20the%20date%20that%20the%20communication%20was%20sent.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20data%20I%20am%20using%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3CSTRONG%3ECommID%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EEmpID%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EEmpName%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3ECategory%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3ESubject%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EViewed%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%3CSTRONG%3ECreated%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E7.72E%2B08%3C%2FTD%3E%3CTD%3E2214626%3C%2FTD%3E%3CTD%3EDoe%2C%20Jon%3C%2FTD%3E%3CTD%3EQuality%20%26amp%3B%20Service%3C%2FTD%3E%3CTD%3EDSAT%20-%203%2F8%20-%20Discussion%20-%20Call%20not%20ava%20-%201%2F16%2F2019%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%2F16%2F2019%2021%3A59%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E7.63E%2B08%3C%2FTD%3E%3CTD%3E2214626%3C%2FTD%3E%3CTD%3EDoe%2C%20Jon%3C%2FTD%3E%3CTD%3EGeneral%3C%2FTD%3E%3CTD%3EDSAT%20-%20Disscussion%20-%20Verbal%20-%20Skill%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E12%2F25%2F2018%2022%3A33%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20return%20the%20Subject%20based%20on%20the%20most%20recent%20entry%20in%20Created.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20currently%20using%20this%20formula%20to%20pull%20the%20data%3A%3C%2FP%3E%3CP%3E%3DIFERROR(INDEX(KPICommDetail%5BSubject%5D%2CMATCH(%5BIdent%5D%2CKPICommDetail%5BEmpID%5D%2C0))%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHelp%20and%20thanks!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-321202%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EINDEX%20MATCH%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-321343%22%20slang%3D%22en-US%22%3ERe%3A%20index%20match%20with%20multiple%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-321343%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20your%20source%20table%20is%20sorted%20on%20date%20your%20formula%20works%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(INDEX(KPICommDetail%5BSubject%5D%2CMATCH(%5B%40Ident%5D%2CKPICommDetail%5BEmpID%5D%2C0))%2C%22%22)%3C%2FPRE%3E%0A%3CP%3EIf%20not%20sorted%2C%20then%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(INDEX(KPICommDetail%5BSubject%5D%2CMATCH(AGGREGATE(14%2C6%2C1%2F(%5B%40Ident%5D%3DKPICommDetail%5BEmpID%5D)*KPICommDetail%5BCreated%5D%2C1)%2CKPICommDetail%5BCreated%5D%2C0))%2C%22%22)%3C%2FPRE%3E%0A%3CP%3EAttached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-321281%22%20slang%3D%22en-US%22%3ERe%3A%20index%20match%20with%20multiple%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-321281%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20but%20I%20couldn't%20get%20that%20to%20work.%20I%20updated%20the%20original%20post%20with%20more%20information%20if%20that%20helps.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-321210%22%20slang%3D%22en-US%22%3ERe%3A%20index%20match%20with%20multiple%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-321210%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(INDEX(Table2%5Bsubject%5D%2CAGGREGATE(14%2C6%2C1%2F(Table2%5Bempid%5D%3D%5B%40empid%5D)*(%20%20ROW(Table2%5Bempid%5D)%20-%20ROW(Table2%5B%5B%23Headers%5D%2C%5Bempid%5D%5D))%2C1))%2C%22%22)%3C%2FPRE%3E%0A%3CP%3Eor%2C%20if%20you%20are%20on%20Office365%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(INDEX(Table2%5Bsubject%5D%2CMATCH(1%2CINDEX((Table2%5Bempid%5D%3D%5B%40empid%5D)*(Table2%5Bdate%5D%3DMAXIFS(Table2%5Bdate%5D%2CTable2%5Bempid%5D%2C%5B%40empid%5D))%2C0)%2C0))%2C%22%22)%3C%2FPRE%3E%0A%3CP%3EBoth%20are%20in%20attached%20file%3C%2FP%3E%3C%2FLINGO-BODY%3E
Jbowyer86
New 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

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies