Formula for picking multiple names and displaying them

[stryped]

I have a spreadsheet as follows:
Department Shift Name

A-End 1 Fred	​
B-End 1 Don	​
B-End 1 Ralph	​
A-End 2 Jason	​

I need a formula that will look through the department column for "B-End", the shift column for "1", and provide the names of all the people that match the above criteria. In this case, it should be "Don", and "Ralph".
Can anyone help me?

 

[Jeffrey Mahoney]

Hi Stryped. What version of Excel do you have? You should consider changing your profile to show that. I could offer you a solution that needs Office 365.

 

[Jeffrey Mahoney]

=TEXTJOIN(",",TRUE,UNIQUE(FILTER(C1:C4,(A1:A4="B-End")*(B1:B4=1),"")))

 

[stryped]

Hi Stryped. What version of Excel do you have? You should consider changing your profile to show that. I could offer you a solution that needs Office 365.

Unfortunately, I have 2019

 

[Fluff]

I have 2019

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Jeffrey Mahoney]

Please try this. Column D has a comma space in each cell

Book5
	A	B	C	D	E	F
1	Department	Shift	Name
2	A-End	1	Fred	,		Don, Ralph,
3	B-End	1	Don	,
4	B-End	1	Ralph	,
5	A-End	2	Jason
Sheet1

Cell Formulas
Range	Formula
F2	F2	=CONCAT(IF((A2:A5="B-End")*(B2:B5=1),C2:D5,""))

 

[Fluff]

Another option

=TEXTJOIN(",",,IF(A2:A100="B-end",B2:B100,""))

This needs to be confirmed with Ctrl Shift Enter, rather than just Enter.

 

[Jeffrey Mahoney]

@Fluff > Column B needs to equal 1, column C are the names. I need to get a list of functions with their respective Excel Version.

 

[Fluff]

Oops, missed that, it should be

=TEXTJOIN(",",,IF((A2:A100="B-end")*(B2:B100=1),C2:C100,""))

 

[stryped]

Please try this. Column D has a comma space in each cell

Book5
	A	B	C	D	E	F
1	Department	Shift	Name
2	A-End	1	Fred	,		Don, Ralph,
3	B-End	1	Don	,
4	B-End	1	Ralph	,
5	A-End	2	Jason
Sheet1

Cell Formulas
Range	Formula
F2	F2	=CONCAT(IF((A2:A5="B-End")*(B2:B5=1),C2:D5,""))

Thanks!