Conditional formatting with custom formula

1
votes

I cannot seem to make my conditional formatting work with a custom formula. The long and short of it is that the formatting is based on some VLOOKUPs. I've tested the formula in a normal cell and it outputs 1 as expected.

=IF(REGEXMATCH(VLOOKUP(C5,CL!C2:H99,5,FALSE), VLOOKUP(B5,CL!J3:K110,2,FALSE)), 1, 0)

I'm basically testing to see whether a certain tag is included in a cell that contains a comma separated list of tags.

The documentation seems to suggest that I need to enter the formula into the box with quotation marks around it (""). I've tried all variations I believe.

I've also tried removing the IF statement, as REGEXMATCH outputs true or false.

Any clue why this isn't working?

1
google-sheetsgs-conditional-formatting

1 Answers

1
votes

when attempting conditional formatting to reference another sheet you need to wrap it into INDIRECT - that's where Google documentation failed. try:

=IF(REGEXMATCH(VLOOKUP(C5, INDIRECT("CL!C2:H99"), 5, 0), 
               VLOOKUP(B5, INDIRECT("CL!J3:K110"), 2, 0)), 1)