'Formula working in cell but not in conditional formatting

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!

arraysif-statementgoogle-sheetsvlookupconditional-formatting


Solution 1:[1]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions