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
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 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
30 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies