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

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:

CodePudding user response：

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!