Search code examples
if-statementgoogle-sheetsconcatenationgoogle-sheets-formulaarray-formulas

Combining two formulas ArrayFormula w/ if and TextJoin not workingI

I am trying to combine two formulas

=TEXTJOIN("|", 1, AQ2, AR2)

If I drag this down each row independently gets joined

And

=ARRAYFORMULA({"AAA";IF(INDIRECT("Elements!D2:D")="Person","Yes", "No")})

I want to combine then

=ARRAYFORMULA({"AAA";IF(INDIRECT("Elements!D2:D")="Person",TEXTJOIN("|", 1, AQ2, AR2), "No")})

But this only expands the first join at A2 and copies it down

How do you combine the formulas, so each row independently gets joined like the manually dragged down version

I have tried adding INDIRECT(AQ2:AQ) and INDIRECT(AR2:AR) in the TextJoin formula but this does not work

Google sheet https://docs.google.com/spreadsheets/d/1uOpOi41kjVWIRO__0y7jg0JKrJNy04Kv1O9jxQWmKjo/edit?usp=sharing

Solution

  • try:

    =ARRAYFORMULA({"AAA"; IF(B2:B="Person", C2:C&IF(D2:D="",,"|"&D2:D), "No")})

    0

    to remove those No's for blank rows use:

    =ARRAYFORMULA({"AAA"; IF(B2:B="",,IF(B2:B="Person", C2:C&IF(D2:D="",,"|"&D2:D), "No"))})