Search code examples
regexgoogle-sheetsgoogle-sheets-formulags-conditional-formatting

Can you make google sheet's 'OR' exit early in the event of an early match?

I have a conditional format that requires around 23 'OR' options. How can I make it only run on cells with text in them, and then also exit early as soon as ONE match is found. Here is the conditional format code:

=IF(OR(
AND(REGEXMATCH($F2, "Action"),($K$3=TRUE)),
AND(REGEXMATCH($F2, "Adventure"),($K$4=TRUE)),
AND(REGEXMATCH($F2, "Animation"),($K$5=TRUE)),
AND(REGEXMATCH($F2, "Comedy"),($K$6=TRUE)),
AND(REGEXMATCH($F2, "Crime"),($K$6=TRUE)),
AND(REGEXMATCH($F2, "Drama"),($K$7=TRUE)),
AND(REGEXMATCH($F2, "Family"),($K$8=TRUE)),
AND(REGEXMATCH($F2, "Fantasy"),($K$9=TRUE)),
AND(REGEXMATCH($F2, "Historical"),($K$10=TRUE)),
AND(REGEXMATCH($F2, "Horror"),($K$11=TRUE)),
AND(REGEXMATCH($F2, "Medical"),($K$12=TRUE)),
AND(REGEXMATCH($F2, "Musical"),($K$13=TRUE)),
AND(REGEXMATCH($F2, "Paranormal"),($K$14=TRUE)),
AND(REGEXMATCH($F2, "Romance"),($K$15=TRUE)),
AND(REGEXMATCH($F2, "Sci-Fi"),($K$16=TRUE)),
AND(REGEXMATCH($F2, "Sport"),($K$17=TRUE)),
AND(REGEXMATCH($F2, "Spy"),($K$18=TRUE)),
AND(REGEXMATCH($F2, "Superhero"),($K$19=TRUE)),
AND(REGEXMATCH($F2, "Thriller"),($K$20=TRUE)),
AND(REGEXMATCH($F2, "War"),($K$21=TRUE)),
AND(REGEXMATCH($F2, "Western"),($K$22=TRUE)),
AND(REGEXMATCH($F2, "Zombie"),($K$23=TRUE))),
1, 0)

Since it runs on a fair few cells, making it exit early, or optimising in some way, would be incredibly helpful. Thanks for any help you can provide. I have tried putting an AND before the or, with the extra condition of NOT(ISBLANK(cell)) but it still runs the OR checks anyway (hence long processing time)

Edit:more information

Solution

  • After reading your most recent comments, Frogglet, it occurred to me that my previous short CF formula will work with genres in one column given a little modification. Personally, I still think six columns is less prone to error and easier to add to; but if you want to keep the setup you have:

    =ArrayFormula(($A2<>"")*(COUNTA(IFERROR(FILTER(TRANSPOSE(TRIM(SPLIT($F2,","))),MATCH(TRANSPOSE(TRIM(SPLIT($F2,","))),FILTER($J$3:$J$50,$K$3:$K$50=TRUE),0))))>0))

    I added a second sheet called "Erik 2" which implements this CF formula.

    All I did was replace the previous six-column range with a TRANSPOSEd SPLIT of your single-column range.

    In addition, this formula is "light," so it can be applied to the entire A2:F range instead of limiting and constantly changing the range to which CF is applied.