Index and Match to exclude certain values

plotting

New Member
Joined
Jun 13, 2016
Messages
17
PositionTarget
QBFade
RBFade
WR1Nelson Agholor
WR2Fade
WR3Fade
TEZach Ertz
QBFade
RBAlex Collins
WR1Fade
WR2Fade
WR3Fade
TEFade

<tbody>
</tbody>


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.
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,280
Office Version
  1. 365
Platform
  1. Windows
Like this?

Excel Workbook
ABCD
1PositionTargetNot Fade
2QBFadeNelson Agholor
3RBFadeZach Ertz
4WR1Nelson AgholorAlex Collins
5WR2Fade
6WR3Fade
7TEZach Ertz
8QBFade
9RBAlex Collins
10WR1Fade
11WR2Fade
12WR3Fade
13TEFade
List
 
Last edited:

plotting

New Member
Joined
Jun 13, 2016
Messages
17
Like this?

List

ABCD
1PositionTarget Not Fade
2QBFade Nelson Agholor
3RBFade Zach Ertz
4WR1Nelson Agholor Alex Collins
5WR2Fade
6WR3Fade
7TEZach Ertz
8QBFade
9RBAlex Collins
10WR1Fade
11WR2Fade
12WR3Fade
13TEFade

<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>
</tbody>

Spreadsheet Formulas
CellFormula
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,280
Office Version
  1. 365
Platform
  1. Windows
You're welcome. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,308
Messages
5,527,930
Members
409,793
Latest member
mavrik_stet

This Week's Hot Topics

Top