I have tried searching on SO, and certainly have not found solution for similar problems, may be I haven't used the right word to search.
So, Column A & Column B is my database, and Column D shows those rep names which I require as an output. I have tried using FILTER with SEARCH & ISNUMBER Function but it returns only one
=FILTER($A$2:$B$13,ISNUMBER(SEARCH($D$2:$D$4,$A$2:$A$13))=TRUE)
Images shown:
Try using the formula as shown in image below,
• Formula used in cell F2 --> Applicable To Excel 2021 & O365 Users
=FILTER(A2:B13,MMULT(--ISNUMBER(SEARCH(TRANSPOSE(D2:D4),A2:A13)),ROW(D2:D4)^0)=1)
• Formula can be used in cell F2 --> Applicable to All Excel Users Except Excel 2007
=IFERROR(INDEX($A$2:$B$13,
AGGREGATE(15,6,(ROW($A$2:$A$13)-ROW($A$2)+1)/
ISNUMBER(SEARCH(TRANSPOSE($D$2:$D$4),$A$2:$A$13)),
ROW($A1)),{1,2}),"")
And Fill Down!
Two more alternatives,
=LET(list,A2:B13,
p,A2:A13,
q,D2:D4,
x,--BYROW(p,
LAMBDA(a,(SUM(COUNTIF(a,"*"&q&"*"))>0))),
FILTER(list,x=1))
=LET(list,A2:B13,
p,A2:A13,
q,D2:D4,
x,MAP(p,LAMBDA(a,IF(SUM(COUNTIF(a,"*"&q&"*"))>0,a,""))),
FILTER(list,x<>""))