Conditional Formatting of cells within a table using X-Lookup to look for values from a range in a second table.

[balakrd]

Greetings,
I am looking to use a formula to apply conditional formatting for an entire column. The value in the cell is also calculated by formulas. I am trying to apply conditional formatting to this cell and subsequently to the entire column using a formula that uses multiple XLOOKUP functions. See formula below. The conditional formatting rules does not accept this formula.

The goal is to check if a certain value in a cell is outside an upper range value or lower range value defined in a second lookup table using the XLOOKUP and OR.

=OR([@[Equity %]]>=XLOOKUP([@[Portfolio Target]],Target_Allocaton[[#All],[Portfolio Target]],Target_Allocaton[[#All],[Upper Range]],"NF",0,1),[@[Equity %]]<=XLOOKUP([@[Portfolio Target]],Target_Allocaton[[#All],[Portfolio Target]],Target_Allocaton[[#All],[Lower Range]],"NF",0,1))

Thank you in advance for any pointers or if there is a more efficient way to implement conditional formatting.

 

[sijpie]

You can add a column and populate it with this formula. Then hide it (after setting up and testing the cond format, of course). Then have conditional formatting of your required column, by refering to the hidden column.