Multiple matches in workbook

%3CLINGO-SUB%20id%3D%22lingo-sub-1737157%22%20slang%3D%22en-US%22%3EMultiple%20matches%20in%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1737157%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20day%20community%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENeed%20assistance%20please.%3C%2FP%3E%3CP%3EI%20require%20a%20formula%20which%20provides%20me%20with%20all%20the%20relevant%20matched%2Fdupplicated%20data%20and%20not%20only%20the%20first%20match%20found.%3C%2FP%3E%3CP%3EWhen%20comparing%20sheet%201%20with%20sheet%202%20the%20result%20should%20tell%20me%20that%20the%20data%20is%20matched%20in%20row%201%20%2C%202%20%2C%203...etc%20on%20sheet%202.%3C%2FP%3E%3CP%3EI%60ve%20been%20using%20a%20normal%20match%20formula%20but%20it%20only%20returns%20the%20first%20match%20found.%3C%2FP%3E%3CP%3EI%60ve%20been%20reading%20about%20the%20Aggregate%20function%20but%20not%20applying%20it%20correctly.%3C%2FP%3E%3CP%3ESee%20my%20example%20below%3A%3C%2FP%3E%3CP%3ESheet%201%3C%2FP%3E%3CTABLE%20width%3D%22519%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%3EProduct%3C%2FTD%3E%3CTD%20width%3D%22170%22%3EDate%3C%2FTD%3E%3CTD%20width%3D%2281%22%3EPrice%3C%2FTD%3E%3CTD%20width%3D%22204%22%3EDupplication%20result%20in%20Sheet%202%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECar%3C%2FTD%3E%3CTD%3E02.10.2020%3C%2FTD%3E%3CTD%3E500%20000%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EChair%3C%2FTD%3E%3CTD%3E01.02.2020%3C%2FTD%3E%3CTD%3E2000%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EMirror%3C%2FTD%3E%3CTD%3E01.02.2020%3C%2FTD%3E%3CTD%3E400%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EGrass%3C%2FTD%3E%3CTD%3E10.09.2020%3C%2FTD%3E%3CTD%3E300%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESheet%202%3C%2FP%3E%3CTABLE%20width%3D%22315%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%3EProduct%3C%2FTD%3E%3CTD%20width%3D%22170%22%3EDate%3C%2FTD%3E%3CTD%20width%3D%2281%22%3EPrice%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECar%3C%2FTD%3E%3CTD%3E02.10.2020%3C%2FTD%3E%3CTD%3E500%20000%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECar%3C%2FTD%3E%3CTD%3E02.10.2020%3C%2FTD%3E%3CTD%3E500%20000%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EMirror%3C%2FTD%3E%3CTD%3E01.02.2020%3C%2FTD%3E%3CTD%3E400%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EMirror%3C%2FTD%3E%3CTD%3E01.02.2020%3C%2FTD%3E%3CTD%3E400%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20assist.%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1737157%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1738953%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20matches%20in%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1738953%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F760420%22%20target%3D%22_blank%22%3E%40jbbarnabas%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20D2%20on%20Sheet%201%2C%20as%20an%20%3CSTRONG%3Earray%20formula%3C%2FSTRONG%3E%20confirmed%20with%20%3CSTRONG%3ECtrl%2BShift%2BEnter%3C%2FSTRONG%3E%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DTEXTJOIN(%22%2C%22%2CTRUE%2CIF(('Sheet%202'!%24A%242%3A%24A%24200%3DA2)*('Sheet%202'!%24B%242%3A%24B%24200%3DB2)*('Sheet%202'!%24C%242%3A%24C%24200%3DC2)%2CROW('Sheet%202'!%24A%242%3A%24A%24200)%2C%22%22))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EChange%20Sheet%202%20to%20the%20actual%20name%20of%20the%20second%20sheet%2C%20and%20adjust%20the%20ranges%20if%20Sheet%202%20has%20more%20than%20200%20rows%20of%20data.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThen%20fill%20down.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Good day community

 

Need assistance please.

I require a formula which provides me with all the relevant matched/dupplicated data and not only the first match found.

When comparing sheet 1 with sheet 2 the result should tell me that the data is matched in row 1 , 2 , 3...etc on sheet 2.

I`ve been using a normal match formula but it only returns the first match found.

I`ve been reading about the Aggregate function but not applying it correctly.

See my example below:

Sheet 1

ProductDatePriceDupplication result in Sheet 2
Car02.10.2020500 000 
Chair01.02.20202000 
Mirror01.02.2020400 
Grass10.09.2020300 

 

Sheet 2

ProductDatePrice
Car02.10.2020500 000
Car02.10.2020500 000
Mirror01.02.2020400
Mirror01.02.2020400

 

Please assist.

Thanks

1 Reply
Highlighted

@jbbarnabas 

In D2 on Sheet 1, as an array formula confirmed with Ctrl+Shift+Enter:

 

=TEXTJOIN(",",TRUE,IF(('Sheet 2'!$A$2:$A$200=A2)*('Sheet 2'!$B$2:$B$200=B2)*('Sheet 2'!$C$2:$C$200=C2),ROW('Sheet 2'!$A$2:$A$200),""))

 

Change Sheet 2 to the actual name of the second sheet, and adjust the ranges if Sheet 2 has more than 200 rows of data.

 

Then fill down.