Arrayformula does not expand past first row-CodePudding

=ArrayFormula({"test"; IF(AND(Q2:Q="true", R2:R="true"), "match", "")})

For some reason I am only getting a value in row 2. Am I missing something?

CodePudding user response：

can you try either & see if it works:

=ArrayFormula({"test"; IF((Q2:Q=TRUE)*(R2:R=TRUE), "match", "")})

=ArrayFormula({"test"; IF((Q2:Q="true")*(R2:R="true"), "match", "")})

CodePudding user response：

As rockinfreakshow correctly stated, there are some functions that you cannot use with ARRAYFORMULA. Two of them are AND and OR, so the way you work it out is to use multiplications for conditions that are needed to be met together (AND) and sums for conditions that needed to be met one or more of them (OR)

So, picturing the TRUEs and FALSEs as 1s or 0s, if you have one condition not met in AND all the result will return 0 (110*1 = 0) and with just one condition met in OR you'll have a TRUE value (0 0 1 0= 1, 0 1 1 1 = 3, any number over 0 will be considered as TRUE).

All that said, you can express your conditions:

=ARRAYFORMULA({"test"; IF((Q2:Q=TRUE)*(R2:R=TRUE), "match", "")})

={"test"; ARRAYFORMULA(IF((Q2:Q=TRUE)*(R2:R=TRUE), "match", ""))}