Using XLOOKUP in Conditional Formatting

[Mortiray]

I'm having trouble getting a conditional formatting rule to work. For context, I have three columns - a ship date, a deliver date, and a ship method. I have a table full of possible ship methods, and the associated number of days it takes to ship using that method, and I want my ship method cell to light up red if it'll be too slow for the shipping request. When I type the following formula:

=AND($P4-$O4 > XLOOKUP($R4, ShippingMethodList[ShippingMethods], ShippingMethodList[Days], 100), ROW() > 3)

into a cell on the worksheet, it works perfectly fine (returns TRUE or FALSE), but when I paste this exact code into my conditional formatting rule, it gives me a "There's a problem with this formula" error. Any idea why?

 

[Fluff]

You cannot use structured references in CF you will need to change them to ranges.

 

[Mortiray]

You cannot use structured references in CF you will need to change them to ranges.

lame. I changed it to

=AND($P4-$O4 > XLOOKUP($R4, Metadata!$C$2:$C$18, Metadata!$D$2:$D$18, 100), ROW() > 3)

and it worked, thanks

 

[Fluff]

Glad you sorted it & thanks for the feedback.