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

Question

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

Answer 1

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.

Answer 2

Frogglet, see my comments above. But, for instance, just based on what I see in your formula and using a little creativity (assuming I'm right about the fact that your formula pairing both "Comedy" and "Crime" with $K$6 being a mistake, and therefore everything from there down being wrong), here's a much shorter formula that would accomplish the same thing:

=IFERROR(IF(INDIRECT("K"&SEARCH(LEFT($F2,3),"XXACTADVANICOMCRIDRAFAMFANHISHORMIDMUSPARROMSCISPOSPYSUPTHRWARWESZOM")/3+2)=TRUE,1,0))

I don't see why you'd be using REGEXMATCH at all.

What I did here was basically form a long string from the first three letters of each of your genres, bumping it in two with the "XX" up front so that every start of a genre-chunk is found at positions divisible by 3. Then I searched for the first three letters, found the location, divided by three and added 2 to offset to where your Column-K locations for TRUE start.

But in real life, if I could see your sheet, it would be even shorter by far; because we'd set up your genre list in a separate sheet as a reference, use it for validation in F2 and then INDEX(MATCH()) to get the right pairing with Column K.

Answer 3

Seeing your sheet makes things clear, Frogglet.

I set up a new sheet called "Erik" in your sample spreadsheet. I changed the format so that your previous comma-separated list in F:F is broken up into columns F:K. These columns use Data Validation based on your complete genre-list column, which A.) makes it easy for you to select genres quickly and B.) assures you don't mistype anything. It also makes writing a CF formula much easier.

The CF custom formula I could now apply to A2:K is as follows:

=ArrayFormula(($A2<>"")*(COUNTA(IFERROR(FILTER(TRANSPOSE($F2:$K2),MATCH(TRANSPOSE($F2:$K2),FILTER($O$3:$O$50,$P$3:$P$50=TRUE),0))))>0))

Personally, I would have put your genre checklist in its own sheet. But it works fine as-is (as long as you only do your sorting on Columns A:K and not on entire rows).

Answer 4

try:

=N(REGEXMATCH(F2, TEXTJOIN("|", 1, FILTER(J:J, K:K=TRUE))))