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


[TABLE="width: 522"]
<tbody>[TR]
[TD]Col A
[/TD]
[TD]Col B
[/TD]
[TD]Col C
[/TD]
[TD]Col D
[/TD]
[TD]Col E
[/TD]
[/TR]
[TR]
[TD]Task
[/TD]
[TD="colspan: 3"]Team
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TASK1
[/TD]
[TD]b1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]TASK3
[/TD]
[/TR]
[TR]
[TD]TASK2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TASK3
[/TD]
[TD][/TD]
[TD]B1
[/TD]
[TD][/TD]
[TD]TASK7
[/TD]
[/TR]
[TR]
[TD]TASK4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TASK5
[/TD]
[TD][/TD]
[TD][/TD]
[TD]B1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TASK6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TASK7
[/TD]
[TD][/TD]
[TD]B1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TASK8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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