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.

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Employee Name[/TD]
[TD]Paid due?[/TD]
[/TR]
[TR]
[TD]Bobby Jones[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]Richard Green[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Frank James[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Michelle Coolidge[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Allie Grayman[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Laura Finch[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Steve Heller[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Jason Richie[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Patty Jones[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]Bill Musgrave[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Cameron Fuller[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Graham Yoland[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Katie Tegerine[/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]

Desired result:

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Richard Green[/TD]
[/TR]
[TR]
[TD]Allie Grayman[/TD]
[/TR]
[TR]
[TD]Laura Finch[/TD]
[/TR]
[TR]
[TD]Patty Jones[/TD]
[/TR]
[TR]
[TD]Bill Musgrave[/TD]
[/TR]
[TR]
[TD]Cameron Fuller[/TD]
[/TR]
</tbody>[/TABLE]


Thank you!

 

[Marcelo Branco]

Try


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Employee Name​

[/TD]
[TD]

Paid due?​

[/TD]
[TD][/TD]
[TD]

Criteria​

[/TD]
[TD]

List​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Bobby Jones​

[/TD]
[TD]

No​

[/TD]
[TD][/TD]
[TD]

Yes​

[/TD]
[TD]

Richard Green​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Richard Green​

[/TD]
[TD]

Yes​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Allie Grayman​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Frank James​

[/TD]
[TD]

No​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Laura Finch​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Michelle Coolidge​

[/TD]
[TD]

No​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Patty Jones​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Allie Grayman​

[/TD]
[TD]

Yes​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Bill Musgrave​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Laura Finch​

[/TD]
[TD]

Yes​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Cameron Fuller​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

Steve Heller​

[/TD]
[TD]

No​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

Jason Richie​

[/TD]
[TD]

No​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

Patty Jones​

[/TD]
[TD]

Yes​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

Bill Musgrave​

[/TD]
[TD]

Yes​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

Cameron Fuller​

[/TD]
[TD]

Yes​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD]

Graham Yoland​

[/TD]
[TD]

No​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD]

Katie Tegerine​

[/TD]
[TD]

No​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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!