Multiple matches in workbook

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 


Sheet 2

Car02.10.2020500 000
Car02.10.2020500 000


Please assist.


1 Reply


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.