Google Sheets conditional formatting formula with dynamic lookup from right to left

Question

I have a Google Sheet where I am attempting to apply conditional formatting to the even cells in any given column if their values are smaller than the value in the first cell to the left of it on the row below it which contains data, looking from right to left.

To illustrate the issue, see cells I6, F10, J14, and K22. Their related cells on the odd row below are indicated by a lighter shade of the same colour, with the cell whose data needs to be compared in bold text.

So for I6, the conditional formatting formula needs to first look at cell H7 for data, and if there is none, then G7, F7, E7, and finally D7. No data in any of the cells means conditional formatting will not apply, but the first of these cells to contain data will be the data used for the comparison, so in this case, F7's data is used, and if it's data is larger than the data in I6, I6 will have the conditional formatting applied.

I have this working for the cells in F6:F33 by the use of this conditional formatting formula:

=AND(ISNUMBER(F6),OR(AND(ISNUMBER(E7),F6<E7),AND(ISBLANK(E7),ISNUMBER(D7),F6<D7)))

..but it only works for this specific range. To avoid manually adjusting it for each column, is there a way it could dynamically perform the same function so I can use the same formula for any number of columns?

Answer 1

Alternative custom formula using reverse lookup with Xlookup:

=ArrayFormula(iseven(row(D6))*(D6<>"")*(D6<xlookup(true,($C7:C7<>""),n($C7:C7),,0,-1)))

applied to D6:K33

Answer 2

You may try:

Apply to range: E5:K33

Custom formula is:

=iseven(row())*(isnumber(E5))*(let(Ξ,choosecols(let(Σ,columns($D5:E5)-1,Λ,offset(E5,1,-Σ,,Σ),filter(Λ,Λ<>"")),-1),(isnumber(Ξ))*(E5<Ξ)))