Lookup and Concatenate

[Pestomania]

Hello, I am trying to identify how to Create a "statement" based on a lookup of the workaround based on where Yes is listed in the row.

Prestons Playground for Modeling 1.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Column1	Reprioritize	VPN	Alternate Location	Manual Process	Statement			Status	Workaround
2	Action 1	Yes		Yes		Reprioritize Workload, Move to an alternate location on Campus			Reprioritize	Reprioritize Workload
3	Action 2		Yes		Yes	Utilize VPN, Manual Process available			VPN	Utilize VPN
4	Action 3	Yes	Yes	Yes					Alternate Location	Move to an alternate location on Campus
5	Action 4		Yes	Yes	Yes				Manual Process	Manual Process available
6									Other Staff	Can cover each other
7									ICT Staff	Utilize Wichita Staff
8
9									I would like to have a vlookup based on where the "Yes" is. And once the workaround has been identified, I would like to have ti concatenate the workaround, comma, and next workaround until none left.
10
11
12
Sheet9

Cell Formulas
Range	Formula
I2:I5	I2	=TRANSPOSE(UNIQUE(Table11[[#Headers],[Reprioritize]:[Manual Process]]))
F2:F3	F2	=J2&", "&J4
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B2:E5	List	Yes, No

 

[Cubist]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Column1	Reprioritize	VPN	Alternate Location	Manual Process	Statement			Status	Workaround
2	Action 1	Yes		Yes		Reprioritize Workload, Move to an alternate location on Campus			Reprioritize	Reprioritize Workload
3	Action 2		Yes		Yes	Utilize VPN, Manual Process available			VPN	Utilize VPN
4	Action 3	Yes	Yes	Yes		Reprioritize Workload, Utilize VPN, Move to an alternate location on Campus			Alternate Location	Move to an alternate location on Campus
5	Action 4		Yes	Yes	Yes	Utilize VPN, Move to an alternate location on Campus, Manual Process available			Manual Process	Manual Process available
6									Other Staff	Can cover each other
7									ICT Staff	Utilize Wichita Staff
Sheet11

Cell Formulas
Range	Formula
I2:I5	I2	=TRANSPOSE(B1:E1)
F2:F5	F2	=TEXTJOIN(", ",,IF(B2:E2="Yes",XLOOKUP($B$1:$E$1,$I$2:$I$7,$J$2:$J$7),""))
Dynamic array formulas.

 

[Pestomania]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Column1	Reprioritize	VPN	Alternate Location	Manual Process	Statement			Status	Workaround
2	Action 1	Yes		Yes		Reprioritize Workload, Move to an alternate location on Campus			Reprioritize	Reprioritize Workload
3	Action 2		Yes		Yes	Utilize VPN, Manual Process available			VPN	Utilize VPN
4	Action 3	Yes	Yes	Yes		Reprioritize Workload, Utilize VPN, Move to an alternate location on Campus			Alternate Location	Move to an alternate location on Campus
5	Action 4		Yes	Yes	Yes	Utilize VPN, Move to an alternate location on Campus, Manual Process available			Manual Process	Manual Process available
6									Other Staff	Can cover each other
7									ICT Staff	Utilize Wichita Staff
Sheet11

Cell Formulas
Range	Formula
I2:I5	I2	=TRANSPOSE(B1:E1)
F2:F5	F2	=TEXTJOIN(", ",,IF(B2:E2="Yes",XLOOKUP($B$1:$E$1,$I$2:$I$7,$J$2:$J$7),""))
Dynamic array formulas.

Awesome!!! I wish I could remember textjoin.