Index and Match to exclude certain values

[plotting]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Position[/TD]
[TD]Target[/TD]
[/TR]
[TR]
[TD]QB[/TD]
[TD]Fade
[/TD]
[/TR]
[TR]
[TD]RB[/TD]
[TD]Fade[/TD]
[/TR]
[TR]
[TD]WR1[/TD]
[TD]Nelson Agholor[/TD]
[/TR]
[TR]
[TD]WR2[/TD]
[TD]Fade[/TD]
[/TR]
[TR]
[TD]WR3[/TD]
[TD]Fade[/TD]
[/TR]
[TR]
[TD]TE[/TD]
[TD]Zach Ertz[/TD]
[/TR]
[TR]
[TD]QB[/TD]
[TD]Fade[/TD]
[/TR]
[TR]
[TD]RB[/TD]
[TD]Alex Collins[/TD]
[/TR]
[TR]
[TD]WR1[/TD]
[TD]Fade[/TD]
[/TR]
[TR]
[TD]WR2[/TD]
[TD]Fade[/TD]
[/TR]
[TR]
[TD]WR3[/TD]
[TD]Fade[/TD]
[/TR]
[TR]
[TD]TE[/TD]
[TD]Fade[/TD]
[/TR]
</tbody>[/TABLE]


I have the following data, on another sheet I want to pull all the names in column B <> "Fade"
I was trying an array formula like this: =IF(ISERROR(SMALL(IF($B$2:$B$13<>"Fade",ROW($A$2:$A$13)),ROW())),"",INDEX($B$2:$B$13,SMALL(IF($B$2:$B$13<>"Fade",ROW($A$2:$A$13)),ROW())-1)) but I just can't get it to work. Any help would be appreciated.

 

[Peter_SSs]

Like this?

Excel Workbook
	A	B	C	D
1	Position	Target		Not Fade
2	QB	Fade		Nelson Agholor
3	RB	Fade		Zach Ertz
4	WR1	Nelson Agholor		Alex Collins
5	WR2	Fade
6	WR3	Fade
7	TE	Zach Ertz
8	QB	Fade
9	RB	Alex Collins
10	WR1	Fade
11	WR2	Fade
12	WR3	Fade
13	TE	Fade
List

 

[plotting]

Like this?

List

	A	B	C	D
Position	Target		Not Fade
QB	Fade		Nelson Agholor
RB	Fade		Zach Ertz
WR1	Nelson Agholor		Alex Collins
WR2	Fade
WR3	Fade
TE	Zach Ertz
QB	Fade
RB	Alex Collins
WR1	Fade
WR2	Fade
WR3	Fade
TE	Fade

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:79px;"><col style="width:133px;"><col style="width:23px;"><col style="width:122px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula D2 =IFERROR(INDEX(B$2:B$13,AGGREGATE(15,6,(ROW(B$2:B$13)-ROW(B$2)+1)/(B$2:B$13<>"Fade"),ROWS(D$2:D2))),"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Excel genius again! That worked like a charm, thanks a ton, going to have to learn aggregate more and start using that

 

[Peter_SSs]

You're welcome.