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?