Why does my conditional formatting not work with this formula?

Question

Below is a formula I want to use in conditional formatting. Its purpose is to blur a range in a text concatenation table if a checkbox B15 is unchecked and if the left and right of the string matches any value from 'Code Sheet'!X4:AD4 (m, ɱ, n, ɳ, ɲ, ŋ, or ɴ).

=iferror(and($B$15=false, 
if(match(left(C19,1),{'Code Sheet'!X4:AD4},0),
if(match(right(C19,1),{'Code Sheet'!X4:AD4},0),
true,false))),false)

The conditional formatting should blur any combo of the aforementioned values. But my formula does not work outside of a cell. The conditional formatting box turns red without feedback. I have no idea why. Where am I going wrong?

Note: the concatenation table has the range of C19:CH102, and the table filters and transposes values - meaning the formula must also adapt to changes in data position.

Answer 1

Use indirect() when referring to another sheet in a conditional formatting custom formula rule, like this:

=not($B$15) * ifna(match(left(C19), indirect("Code Sheet!X4:AD4"), 0)) * ifna(match(right(C19), indirect("Code Sheet!X4:AD4"), 0))

The formula will only return a truthy value when all three conditions are met. To use an "or" condition that returns a truthy value when any of the conditions is met, replace the * with +. To always require the first condition, but accept either of the two following conditions, use first * ( second + third ). See Boolean arithmetic.