Text IF statement with multiple criteria returning multiple values

[k_one]

Hi there,

Just wondering if there's a possible formula to help with the below.

I am after a formula for Column E that returns certain text when a cell/cells are not blank. So for row 2 for example, because there are values in only Column A and D; the result I am looking for would be the names of System One and System Four.

All the cells are text and the system names are also text. Is this doable at all?

Name in System One	Name in System Two	Name in System Three	Name in System Four	Systems
Apple			Watermelon	System One, System Four
Pear	Banana			System One, System Two
Orange				System One
Strawberry	Apricot	Peach		System One, System Two, System Three
Dragonfruit	Lychee	Blueberry	Nectarine	System One, System Two, System Three, System Four

<colgroup><col><col span="3"><col></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

In F2 control+shift+enter, not just enter, and copy down:

=TEXTJOIN(", ",TRUE,IF($A2:$D2="","",REPLACE($A$1:$D$1,1,8,"")))