Forum Discussion

HZ2506's avatar
HZ2506
Copper Contributor
Nov 10, 2022

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, 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,

BI & Data Analysis
excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Nov 10, 2022

    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.

     

    • HZ2506's avatar
      HZ2506
      Copper Contributor
      Nov 10, 2022

      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
      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        Nov 11, 2022

        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.

Resources