Make a list with formula based on criteria

Stuepef

Board Regular
Joined
Oct 23, 2017
Messages
110
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 NamePaid due?
Bobby JonesNo
Richard GreenYes
Frank JamesNo
Michelle CoolidgeNo
Allie GraymanYes
Laura FinchYes
Steve HellerNo
Jason RichieNo
Patty JonesYes
Bill MusgraveYes
Cameron FullerYes
Graham YolandNo
Katie TegerineNo

<tbody>
</tbody>

Desired result:

Richard Green
Allie Grayman
Laura Finch
Patty Jones
Bill Musgrave
Cameron Fuller

<tbody>
</tbody>


Thank you!
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,336
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

Board Regular
Joined
Oct 23, 2017
Messages
110
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,336
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.
 

Forum statistics

Threads
1,085,031
Messages
5,381,303
Members
401,733
Latest member
Kabasa007

Some videos you may like

This Week's Hot Topics

Top