Issue with array formula concatenate with And()-CodePudding

I have a fairly lengthy formula in the sheet located here in cell BO7: https://docs.google.com/spreadsheets/d/1f6IzQ83w7lDajcRmAp6iOFaorl1xNMv7PIE1X1fFfoE/edit?usp=sharing

=Concatenate(char(10),"@",$B7,char(10),"You ",BD7,IF(OR(BM7="",value(LEFT(BC7,1))=9),""," targeting "&BM7),", you have ",BG7," :coin: and ",BJ7," hp.",IF(BL7=""," You belong to no team",CONCATENATE(" You belong to team ",BL7))," and are located at the **",BK7,"**.",IF(BF7="","",CONCATENATE(char(10),BF7)), IF(BH7="","",Concatenate(char(10),char(10),"**In your :school_satchel: you find:** ",char(10),BH7)),char(10),char(10),IF(VALUE(LEFT(BC7,1))=7,"**In your target :7Travel: area you :eye:**"&char(10)&"@"&Arrayformula(textjoin(concatenate(char(10),"@"),TRUE,IF((BK$2:BK=RIGHT(BC7,LEN(BC7)-2)),$B$2:$B,"")))&char(10)&char(10),""),IF(VALUE(LEFT(BC7,1))=7,"**and traveling to your target :7Travel: area you :eye:**"&char(10)&"@"&Arrayformula(textjoin(concatenate(char(10),"@"),TRUE,IF(BM$2:BM=RIGHT(BC7,LEN(BC7)-2),$B$2:$B,"")))&char(10)&char(10),""),IF(AND(BK7="Mountaintop",BC7=8),"","**In your area you** :eye: "&char(10)&"@"&ARRAYFORMULA(TEXTJOIN(concatenate(char(10),"@"),TRUE,IF(BK$2:BK=BK7,$B$2:$B,"")))),char(10),char(10),IF(ISTEXT(IFERROR(VLOOKUP($B7,BM:BM,1,false),TRUE)),concatenate("**You were targeted!** :dart: ",char(10),"@"),""),ARRAYFORMULA(TEXTJOIN(concatenate(char(10),"@"),TRUE,IF(BM$2:BM=$B7,$B$2:$B&" "&BD$2:BD&"!"&char(10),""))),char(10),"Next provide **"&INDIRECT("R1C"&value(match("acts aimlessly",BU7:AOD7,0) COLUMN(BU7)-2),FALSE)&"**"&char(10)&"1. :1FastAttack: "&RIGHT(VLOOKUP($B7&"/"&1,'#s'!$A:$B,2,false),LEN(VLOOKUP($B7&"/"&1,'#s'!$A:$B,2,false))-5)&char(10)&"2. :2NormalAttack: "&RIGHT(VLOOKUP($B7&"/"&2,'#s'!$A:$B,2,false),LEN(VLOOKUP($B7&"/"&2,'#s'!$A:$B,2,false))-5)&char(10)&"3. :3HeavyAttack: "&RIGHT(VLOOKUP($B7&"/"&3,'#s'!$A:$B,2,false),LEN(VLOOKUP($B7&"/"&3,'#s'!$A:$B,2,false))-5)&char(10)&"4. :4Interrupt: Interrupt to deal damage to resting and evading opponents"&char(10)&"5. :5Evade: Evade to avoid damage"&char(10)&"6. :6Rest: Rest to heal"&char(10)&"7. :7Travel: Travel to a chosen area"&char(10)&"8. :8AreaAction: Use an Area Action"&char(10)&"9. :9UseanItem: Use an item")

I would like to adjust the formula, so that the section here:

,IF(VALUE(LEFT(BC7,1))=7,"**and traveling to your target :7Travel: area you :eye:**"&char(10)&"@"&Arrayformula(textjoin(concatenate(char(10),"@"),TRUE,IF(BM$2:BM=RIGHT(BC7,LEN(BC7)-2),$B$2:$B,"")))&char(10)&char(10),""),

returns only the $B$2:$B values where BB$2:BB=1 and BM$2:BM=RIGHT(BC7,LEN(BC7)-2, currently it returns all of the cells that meet the latter condition, but whenever I try to add the former condition it seems to fail.

The solution I tried that did not work was:

,IF(VALUE(LEFT(BC7,1))=7,"**and traveling to your target :7Travel: area you :eye:**"&char(10)&"@"&Arrayformula(textjoin(concatenate(char(10),"@"),TRUE,IF(AND(BM$2:BM=RIGHT(BC7,LEN(BC7)-2),BB$2:BB=1),$B$2:$B,"")))&char(10)&char(10),""),

CodePudding user response：

AND is not supported by ARRAYFORMULA. use multiplication *. example:

instead of:

=ARRAYFORMULA(IF(AND(A1:A="x", B1:B="y"), 1, 0)

use:

=ARRAYFORMULA(IF((A1:A="x")*(B1:B="y"), 1, 0)

for OR logic use instead of *