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?

  • 1
    Stick-a-round Clay as it might take a while for n answer to arrive....Welcome on Web Applications !! – Jacob Jan Tuinstra Jan 23 '15 at 16:59
  • 1
    No worries and thanks for the welcome. – Clay Jan 26 '15 at 14:35
  • 1
    Hi Clay, would it be possible to share a copy of your spreadsheet so we can take a closer look ? – JPV Feb 26 '15 at 9:11

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


AND: Instead of




OR: Instead of




Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.