Home > Back-end >  Formula working in cell but not in conditional formatting
Formula working in cell but not in conditional formatting

Time:04-26

I have the following formula which works as desired when entered into a cell. However, the same formula does not work when I try to use it in conditional formatting:

=IF(REGEXMATCH(VLOOKUP(U2,DEALS!$A$2:F,5,FALSE), "Likes"), O2>=VLOOKUP(U2,DEALS!$A$2:F,4,FALSE), Q2>=VLOOKUP(U2,DEALS!$A$2:F,4,FALSE))

Formula in cell

Formula in conditional formatting

I haven't had any issues with conditional formatting up until now with this formula, and I am really not sure what is the issue as this is not my expertise.

Appreciate any help I can get!

CodePudding user response:

try:

=IF(REGEXMATCH(VLOOKUP(U2, INDIRECT("DEALS!A2:F"), 5, 0), "Likes"), 
 O2>=VLOOKUP(U2, INDIRECT("DEALS!A2:F"), 4, 0), 
 Q2>=VLOOKUP(U2, INDIRECT("DEALS!A2:F"), 4, 0))

conditional formatting does not understand references coming from different sheets if not indirected

enter image description here

  • Related