Offset Match condition

[Sahitya]

My output should have both the fruits which have value as true.

For eg: For third row, Apple, Orange and Watermelon has the value as "True", but it is returning only first value.

Formula i have used : =IFERROR(OFFSET([@Apple],[@[Offset Tower]],MATCH(TRUE,Table2[@[Apple]:[pineapple]],0)-1),"")

Apple	Orange	Banana	Watermelon	pineapple	Offset Tower	Output
FALSE	TRUE	FALSE	FALSE	FALSE	-1	Orange
FALSE	FALSE	FALSE	FALSE	TRUE	-2	pineapple
TRUE	TRUE	FALSE	TRUE	FALSE	-3	Apple
FALSE	TRUE	FALSE	FALSE	FALSE	-4	Orange
FALSE	FALSE	TRUE	FALSE	FALSE	-5	Banana
FALSE	FALSE	FALSE	TRUE	TRUE	-6	Watermelon

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

 

[jorismoerings]

Hi,

There is no native function which can concatenate multiple values in a single cell. You could either get the results in a horizontal multi-cell record or invoke a function written in VBA which constructs a single cell result. A VBA like solution can be found here

 

[Sahitya]

Thanks for your reply. it does match my scenario

 

[Aladin Akyurek]

Thanks for your reply. it does match my scenario

If the native TEXTJOIN function is available on your system...

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

=TEXTJOIN(", ",TRUE,IF($A2:$E2,$A$1:$E$1,""))

 

[Saba Sabaratnam]

You may try the following formula.

Enter the formula in H2

=IF(A2=TRUE,A$1&" ","")&IF(B2=TRUE,B$1&" ","")&IF(C2=TRUE,C$1&" ","")&IF(D2=TRUE,D$1&" ","")&IF(E2=TRUE,E$1&" ","")

 

[Sahitya]

If the native TEXTJOIN function is available on your system...

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

=TEXTJOIN(", ",TRUE,IF($A2:$E2,$A$1:$E$1,""))

Thankyou very much Aladin. It works fine

 

[Aladin Akyurek]

Thankyou very much Aladin. It works fine

You are welcome.