Dynamically listing results from an array?

[hitdave85]

Hello,

On the tab 'Sheet1', I have the following table:

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]New York
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]California
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]Alabama
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]Texas
[/TD]
[TD]Yes
[/TD]
[/TR]
</tbody>[/TABLE]

On the tab 'Sheet2', I would like to only list the states with a 'Yes' from this table. In other words, is it possible via formulas to dynamically list those states on another tab based on my 'Yes' criteria? By dynamic, I mean that I can add more states to my table on 'Sheet1' and the 'Yes' states will automatically populate in a convenient list on a tab on 'Sheet2'? I know VBA can do this, but I'm curious if this can be done via formulas. Any ideas?

Thanks!

 

[Fluff]

On sheet1

Excel 2013/2016
	A	B
1	State	Yes/No
2	New York	Yes
3	California	No
4	Alabama	No
5	Texas	Yes
Sheet1

And sheet 2

Excel 2013/2016
	A
1	States
2	New York
3	Texas
Sheet2

Cell Formulas
Range	Formula
A2	{=IFERROR(INDEX(Sheet1!A$2:A$100,SMALL(IF(Sheet1!B$2:B$100="Yes",ROW(Sheet1!$A$2:$A$100)-ROW(Sheet1!$A$2)+1),ROWS($1:1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Copied down as far as needed

 

[hitdave85]

Awesome! Very cool. Is it possible to include additional criteria to 'Yes'? For example, instead of Yes/No, what if I have Yes/No/Maybe and I want to list only the states with either Yes or Maybe.

 

[Fluff]

Try
=IFERROR(INDEX(Sheet1!A$2:A$100,SMALL(IF((Sheet1!B$2:B$100="Yes")+(Sheet1!B$2:B$100="Maybe"),ROW(Sheet1!$A$2:$A$100)-ROW(Sheet1!$A$2)+1),ROWS($1:1))),"")

 

[hitdave85]

Yep, that did the trick. Thanks for the help!

 

[Fluff]

You're welcome & thanks for the feedback