Search code examples
arraysgoogle-sheetsfiltervlookupconditional-formatting

Weird behavior with conditional formatting

I have added the following two equations to conditional formatting:

Green: =IF(REGEXMATCH(VLOOKUP(X2, INDIRECT("DEALS!$A$2:F"),5, FALSE), "Likes"), R2>=VLOOKUP(X2, INDIRECT("DEALS!$A$2:F"), 4, FALSE), T2>=VLOOKUP(X2, INDIRECT("DEALS!$A$2:F"), 4, FALSE))

Red: =IF(REGEXMATCH(VLOOKUP(X2, INDIRECT("DEALS!$A$2:F"),5, FALSE), "Likes"), NOT(R2>=VLOOKUP(X2, INDIRECT("DEALS!$A$2:F"), 4, FALSE)), NOT(T2>=VLOOKUP(X2, INDIRECT("DEALS!$A$2:F"), 4, FALSE)))

The colors should change accordingly depending on whether the target (views in this case) has been met or not.

Target vs. Live Views

Below I have also added the equation into the cells to check if the logic is correct, which it appears to be (left = green logic, right = red logic).

Logic check in cells

For whatever reason, the first row, despite the target not being met, has decided to select the green color. The row below that is doing the complete opposite. And to top it all off, the last two rows are not selecting a color at all even though I have applied the conditional formatting to the entire column:

Conditional formatting applied to entire column P

I am also experiencing weird behavior when dragging equations within this P column, but do not see this same behavior in other columns that also use conditional formatting:

https://i.gyazo.com/5e002e3d08e8337591573b81d9fc92e2.mp4

This has left me completely baffled, and I am not sure what is going on since the equation's logic does not appear to be the issue.

Appreciate any help I can get with this issue!

For reference, here is the other sheet that the VLOOKUP() function is grabbing from:

DEALS Sheet

Solution

  • do not lock ($) references inside INDIRECT. if stuff is between double quotes it's a text string, not an active reference, and text strings are not affected by dragging.

    for green use:

    =IF(REGEXMATCH(VLOOKUP(Z2, INDIRECT("DEALS!A2:F"), 5, 0), "Likes"), 
 R2>=VLOOKUP(Z2, INDIRECT("DEALS!A2:F"), 4, 0), 
 T2>=VLOOKUP(Z2, INDIRECT("DEALS!A2:F"), 4, 0))

    for red use:

    =IF(REGEXMATCH(VLOOKUP(Z2, INDIRECT("DEALS!A2:F"), 5, 0), "Likes"),   
 NOT(R2>=VLOOKUP(Z2, INDIRECT("DEALS!A2:F"), 4, 0)),   
 NOT(T2>=VLOOKUP(Z2, INDIRECT("DEALS!A2:F"), 4, 0)))

    enter image description here

    demo sheet

    update:

    don't drag anything. use this in P2:

    =ARRAYFORMULA(IFNA(TEXT(VLOOKUP(Z2:Z,DEALS!A2:F,4,0), 
 "#,###,##0")& " " &VLOOKUP(Z2:Z,DEALS!A2:F,5,0)))

    enter image description here