Make a list with formula based on criteria

[Stuepef]

I have a list of employees in a spreadsheet where I record if they paid their dues or not. I am looking for a formula solution to generate a list of employees that have paid. I understand this can easily be achieved by filtering, but in this particular case, I want to use a formula.

Employee Name	Paid due?
Bobby Jones	No
Richard Green	Yes
Frank James	No
Michelle Coolidge	No
Allie Grayman	Yes
Laura Finch	Yes
Steve Heller	No
Jason Richie	No
Patty Jones	Yes
Bill Musgrave	Yes
Cameron Fuller	Yes
Graham Yoland	No
Katie Tegerine	No

<tbody>
</tbody>

Desired result:

Richard Green

Allie Grayman

Laura Finch

Patty Jones

Bill Musgrave

Cameron Fuller

<tbody>
</tbody>

Thank you!

 

[Marcelo Branco]

Try

	A​	B​	C​	D​	E​
1​	Employee Name​	Paid due?​		Criteria​	List​
2​	Bobby Jones​	No​		Yes​	Richard Green​
3​	Richard Green​	Yes​			Allie Grayman​
4​	Frank James​	No​			Laura Finch​
5​	Michelle Coolidge​	No​			Patty Jones​
6​	Allie Grayman​	Yes​			Bill Musgrave​
7​	Laura Finch​	Yes​			Cameron Fuller​
8​	Steve Heller​	No​
9​	Jason Richie​	No​
10​	Patty Jones​	Yes​
11​	Bill Musgrave​	Yes​
12​	Cameron Fuller​	Yes​
13​	Graham Yoland​	No​
14​	Katie Tegerine​	No​
15​

<tbody>
</tbody>

Array formula in E2 copied down
=IFERROR(INDEX(A:A,SMALL(IF(B$2:B$14=D$2,ROW(B$2:B$14)),ROWS(E$2:E2))),"")
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[Stuepef]

Thank you for the reply, is there a way to update the INDEX(A:A part of the formula to a more defined range? For instance, A2:A14?

 

[Marcelo Branco]

Thank you for the reply, is there a way to update the INDEX(A:A part of the formula to a more defined range? For instance, A2:A14?

Try

E2 copied down
=IFERROR(INDEX(A$2:A$14,SMALL(IF(B$2:B$14=D$2,ROW(B$2:B$14)-ROW(B$2)+1),ROWS(E$2:E2))),"")
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[Stuepef]

Works perfectly, thank you!