# Multiple matches in workbook

Occasional Contributor

# Multiple matches in workbook

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

 Product Date Price Dupplication result in Sheet 2 Car 02.10.2020 500 000 Chair 01.02.2020 2000 Mirror 01.02.2020 400 Grass 10.09.2020 300

Sheet 2

 Product Date Price Car 02.10.2020 500 000 Car 02.10.2020 500 000 Mirror 01.02.2020 400 Mirror 01.02.2020 400

Please assist.

Thanks

1 Reply

# Re: Multiple matches in workbook

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.