Return cell value but skip row not matched

[Aton999]

Hi,

I am stuck some how on how to skip a non matched row (Cell Value).

The below data set, contain tasks in Col A. I wanted to return all tasks name in cell E2 that include "B1" in the range of (B2:E10). My formula is =IFERROR(INDEX($A$2:$A$9, SMALL(IF("B1"=B2:D2, ROW(A2:A9)-1,""), ROW()-1)),"") Ctrl+****+Enter. The formula works, but does not skip blanks?

Any help is much appreciated.

Thanks, aton

Col A	Col B	Col C	Col D	Col E
Task	Team
TASK1	b1			TASK3
TASK2
TASK3		B1		TASK7
TASK4
TASK5			B1
TASK6
TASK7		B1
TASK8

<tbody>
</tbody>

 

[Scott T]

Try still need CSE

=IFERROR(INDEX($A$2:$A$9, SMALL(IF("B1"=$B$2:$D$9, ROW($A$2:$A$9)-ROW($A$2)+1), ROWS($A$1:A1))),"")

 

[Aton999]

Try still need CSE

=IFERROR(INDEX($A$2:$A$9, SMALL(IF("B1"=$B$2:$D$9, ROW($A$2:$A$9)-ROW($A$2)+1), ROWS($A$1:A1))),"")

It works, however, if I add "b1" in D2, the formula will list "Task1" twice in col E. Is there away of preventing this?

Thanks, aton

 

[steve the fish]

This perhaps:

=IFERROR(INDEX($A$2:$A$9, SMALL(IF(($B$2:$B$9="B1")+($C$2:$C$9="B1")+($D$2:$D$9="B1"),ROW($A$2:$A$9)-ROW($A$2)+1),ROWS($A$1:A1))),"")

 

[Aton999]

This perhaps:=IFERROR(INDEX($A$2:$A$9, SMALL(IF(($B$2:$B$9="B1")+($C$2:$C$9="B1")+($D$2:$D$9="B1"),ROW($A$2:$A$9)-ROW($A$2)+1),ROWS($A$1:A1))),"")

Thank you Steve, working like a charm. I did learn something new today.Thanks Scott T for the quick response.Cheers, aton