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?


[TABLE="width: 903"]
<colgroup><col><col span="3"><col></colgroup><tbody>[TR]
[TD]Name in System One[/TD]
[TD]Name in System Two[/TD]
[TD]Name in System Three[/TD]
[TD]Name in System Four[/TD]
[TD]Systems[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Watermelon[/TD]
[TD]System One, System Four[/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD]Banana[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]System One, System Two[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]System One[/TD]
[/TR]
[TR]
[TD]Strawberry[/TD]
[TD]Apricot[/TD]
[TD]Peach[/TD]
[TD] [/TD]
[TD]System One, System Two, System Three[/TD]
[/TR]
[TR]
[TD]Dragonfruit[/TD]
[TD]Lychee[/TD]
[TD]Blueberry[/TD]
[TD]Nectarine[/TD]
[TD]System One, System Two, System Three, System Four[/TD]
[/TR]
</tbody>[/TABLE]

 

[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,"")))