Formula breaks subtly in ARRAYFORMULA in Google Sheets

Question

I have a spreadsheet document with a formula comparing a list of URLs to lists on two other sheets within the document.

I desire to know if each URL on the first list is present on the blocklist, whitelist, both, or neither.

Note that I have used named ranges on all three URL columns (minus the header, e.g. 'sheetname'!A2:A1000').

Here is my current formula:

=if(len(REQUESTS),if(AND(countif(BLOCK,REQUESTS),countif(WHITE,REQUESTS)),"ON BOTH",if(countif(BLOCK,REQUESTS),"Blocked",if(countif(WHITE,REQUESTS),"Whitelisted","Pending addition to a list"))),"")

This works as expected, except that I need to drag it down to each row in the sheet containing the requests.

When I add it to an ARRAYFORMULA, the AND no longer works. Any URL present in both BLOCK and WHITE returns "Blocked".

=ARRAYFORMULA(if(len(REQUESTS),if(AND(countif(BLOCK,REQUESTS),countif(WHITE,REQUESTS)),"ON BOTH",if(countif(BLOCK,REQUESTS),"Block",if(countif(WHITE,REQUESTS),"White","Pending addition to a list"))),""))

Actual location(s)  Working formula               With ARRAYFORMULA
neither             Pending addition to a list    Pending addition to a list
white               White                         White
block               Block                         Block
BOTH                ON BOTH                       Block

If it helps, when I swapped the if(White) and if(Block), I get "White" when it is on both.

Any ideas why the addition of ARRAYFORMULA breaks my logic, or an alternative method of getting the formula to repeat as needed?

Answer 1

In array formulas instead of AND and OR functions use * and + respectively.

Example

AND: Instead of

=ArrayFormula(and(A2:A4,B2:B4))

use

=ArrayFormula(A2:A4*B2:B4)

OR: Instead of

=ArrayFormula(or(A2:A4,B2:B4))

use

=ArrayFormula(A2:A4+B2:B4)