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:
>Solution :
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!
