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 ɴ).

if(match(left(C19,1),{'Code Sheet'!X4:AD4},0),
if(match(right(C19,1),{'Code Sheet'!X4:AD4},0),

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.

1 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.

  • Your suggested formula seems to be multiplying values. When I tested it on a cell where the conditions would fail, 0 was the output. When I tested it on a cell where conditions were met, 6 was the output.
    – Ylahris
    Commented Jan 7, 2023 at 18:52
  • That is correct. Any non-zero number will be considered truthy, i.e., interpreted as true, in a conditional formatting custom formula rule. Commented Jan 7, 2023 at 19:08
  • Ok. But the conditional formatting box is still angry, saying "invalid formula".
    – Ylahris
    Commented Jan 7, 2023 at 19:11
  • Look on the sheet labeled "Phonotactics Calculator." docs.google.com/spreadsheets/d/…
    – Ylahris
    Commented Jan 7, 2023 at 19:16
  • Edited the answer. There are currently no values in your sample spreadsheet that would meet the conditions because the checkbox in B15 is ticked. Untick it to find a cluster of matching cells at V38:AB44. Commented Jan 7, 2023 at 19:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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