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


[TABLE="class: grid, width: 572, align: left"]
<colgroup><col><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD]Apple[/TD]
[TD]Orange[/TD]
[TD]Banana[/TD]
[TD]Watermelon[/TD]
[TD]pineapple[/TD]
[TD]Offset Tower[/TD]
[TD]Output[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD]-1[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD]-2[/TD]
[TD]pineapple[/TD]
[/TR]
[TR]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD]-3[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD]-4[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD]-5[/TD]
[TD]Banana[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]TRUE[/TD]
[TD]-6[/TD]
[TD]Watermelon[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.