lundi 3 février 2020

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

Aucun commentaire:

Enregistrer un commentaire