How to search multiple names from a range and return multiple records in excel?

Question

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:

Answer 1

EDIT -- 07-05-2022

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<>""))