Text IF statement with multiple criteria returning multiple values

k_one

New Member
Joined
Mar 22, 2012
Messages
18
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 OneName in System TwoName in System ThreeName in System FourSystems
Apple WatermelonSystem One, System Four
PearBanana System One, System Two
Orange System One
StrawberryApricotPeach System One, System Two, System Three
DragonfruitLycheeBlueberryNectarineSystem One, System Two, System Three, System Four

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

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
In F2 control+shift+enter, not just enter, and copy down:

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

Watch MrExcel Video

Forum statistics

Threads
1,108,502
Messages
5,523,295
Members
409,508
Latest member
Afc

This Week's Hot Topics

Top