我在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")
我想调整一下公式，这样这里的部分：
,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),""),
仅返回$B$2:$B值，其中BB$2:BB=1和BM$2:BM=RIGHT(BC7，LEN(BC7(-2，当前它返回满足后一个条件的所有单元格，但每当我尝试添加前一个条件时，它似乎都失败了。
我尝试的解决方案没有奏效：
,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),""),
ARRAYFORMULA不支持AND。使用乘法CCD_ 3。示例：
而不是：
=ARRAYFORMULA(IF(AND(A1:A="x", B1:B="y"), 1, 0)
用途：
=ARRAYFORMULA(IF((A1:A="x")*(B1:B="y"), 1, 0)
对于OR逻辑，使用+而不是*