Extract all matching text from 2 columns

Copper Contributor

Please help on how can we extract all texts that matching in 2 separate columns. Example as below

 

(1) Column A1(Country list 1): Japan, Korea, Vietnam, China, India, Singapore, Indonesia, Malaysia, UK, Ireland, Thailand

(2) Column A2(Country list 2): Japan, Ireland, UK, Russia, France, Scotland, Brazil, Spain, Mexico, Portugal

(3) Column A3 (All matching): Japan, Ireland, UK,

3 Replies

@HZ2506 

=IFERROR(IF(AND(MATCH(B2,$A$2:$A$11,0),MATCH(INDEX($A$2:$A$11,MATCH(B2,$A$2:$A$11,0)),$B$2:$B$11,0)),B2,""),"")

You can try this formula.

all matching.JPG 

@OliverScheurichthank you for you advise, but my data something like below, have multiple rows, so need to filter the result by rows.....

 

1st column2nd columnmatching
a,b,ca,c,za,c
c,z.dc,zc,z
d,e,ee,d,kd,e
b,a,ca,ba,b
a,b,ca,c,za,c
c,z.dc,zc,z
d,e,ee,d,kd,e
b,a,ca,ba,b
a,b,ca,c,za,c
c,z.dc,zc,z
d,e,ee,d,kd,e
b,a,ca,ba,b

@HZ2506 

You are welcome. This is a completely new question for which you can start a new discussion. My suggestion would be VBA code or Power Query like in the attached file.

power query matching.JPG