Forum Discussion

jbbarnabas's avatar
jbbarnabas
Copper Contributor
Oct 01, 2020

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

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

excel

1 Reply

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Oct 02, 2020

    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.

     

     

Resources