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?
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)