Conditional Formatting with OR condition

[Tipsey]

Good evening,

I am applying a condtional formatting to a range based on an xlookup value and I was wondering if there was a more efficient way of performing this than using an or function.

ie. I would like to from something like:

=OR(XLOOKUP($DD1,$CG$6:$CG$205,$CE$6:$CE$205,"",0,1)=TIMEVALUE("5:15"),XLOOKUP($DD1,$CG$6:$CG$205,$CE$6:$CE$205,"",0,1)=TIMEVALUE("5:00"))

to

=XLOOKUP($DD1,$CG$6:$CG$205,$CE$6:$CE$205,"",0,1)=or(TIMEVALUE("5:15"),TIMEVALUE("5:00"))

I am currently, on looking for 2 values, but if there is more in the future, the more modular format of my target code would make it easier to adapt.

 

[6StringJazzer]

You can do this:

=OR(TEXT(XLOOKUP($DD1,$CG$6:$CG$205,$CE$6:$CE$205,"",0,1),"h:mm")={"5:15","5:00"})

Obviously I can't test this without your data, but a simplified version worked for me.

 

[Tipsey]

Thank you, I appreciate the response and would like to let you know that this achieved the desired result while testing the formula in a cell, but it gave an error putting it into the conditional formatting regarding the use of the array.

 

[6StringJazzer]

Oh, I'm sorry about that. There are some things that work in cells but not CF and I did not consider that an array would not work.

The best I can do is this:

=LET(Xtime,TEXT(XLOOKUP($DD1,$CG$6:$CG$205,$CE$6:$CE$205,"",0,1),"h:mm"),OR(Xtime="5:15",Xtime="5:00"))

 

[Tipsey]

Awesome, that worked marvelously.