How to find text in another column Excel-CodePudding

I have data in column1 and column2, If column1 text matched anywhere in another column2 then result should be like c column.

CodePudding user response：

Formula in C2:

=MAP(B2:B5,LAMBDA(ζ,IFNA(LOOKUP(1,0/SEARCH(A2:A3,ζ),A2:A3),"")))

CodePudding user response：

TEXTSPLIT() and FILTER() with COUNTIFS() may work. Try-

=BYROW(B2:B5,LAMBDA(x,TEXTJOIN(", ",1,FILTER(TEXTSPLIT(x,," "),COUNTIFS(A2:A3,TEXTSPLIT(x,," ")),""))))

I have used TEXTJOIN(), if there any possibility to have multiple keywords.