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),"")
<colgroup><col><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>
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>