Forum Discussion
Extract all matching text from 2 columns
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, Malaysi...
=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.
OliverScheurichthank you for you advise, but my data something like below, have multiple rows, so need to filter the result by rows.....
| 1st column | 2nd column | matching |
| a,b,c | a,c,z | a,c |
| c,z.d | c,z | c,z |
| d,e,e | e,d,k | d,e |
| b,a,c | a,b | a,b |
| a,b,c | a,c,z | a,c |
| c,z.d | c,z | c,z |
| d,e,e | e,d,k | d,e |
| b,a,c | a,b | a,b |
| a,b,c | a,c,z | a,c |
| c,z.d | c,z | c,z |
| d,e,e | e,d,k | d,e |
| b,a,c | a,b | a,b |
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.