Conditional Formatting Based on XLOOKUP value

[psycoperl]

Good Day - I am working on a task where I have a table and I want to highlight via conditional formatting certain cells if a criteria is met. I have a XLookup in Column L that will return either a 0, 1, 2, 3, 4, 5. Based on that value determines which formatting I want to have applied to Columns J and K. However, I am not able to get that formatting working.
I have gotten the formatting for when column G is changed to a value working and for when there are duplicate values in column B. Can you please provide some guidance?

Test.xlsx
	I	J	K	L
1	Automatic Direct Placement?	Auto Direct Placement Descr	Automatics Direct Placement Special Criteria	Column2
2	M205	MTH 2205 Placement		1
3	NTMT	MTH Placement TBD / (Old MTH 7000)		3
4	NTMT	MTH Placement TBD / (Old MTH 7000)		3
5	-NO-	NO AUTO PLCMNT		0
6	-NO-	NO AUTO PLCMNT		0
7	-NO-	NO AUTO PLCMNT		0
sheet1

Cell Formulas
Range	Formula
J2:J7	J2	=IF(NOT(ISBLANK(sheet1!$I2)),XLOOKUP(sheet1!$I2,Sheet3!A$1:A$18,Sheet3!C$1:C$18,"",0,1),"")
L2:L7	L2	=XLOOKUP($I2,Sheet3!A$1:A$18,Sheet3!D$1:D$18,"",0,1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I2:K2152	Expression	=ISNUMBER(MATCH($I2,Sheet3!$B$3:$B$16,0))	text	NO
A2:K2152	Expression	=$G2=Sheet2!$A$3	text	NO
A2:K2152	Expression	=$G2=Sheet2!$A$1	text	NO
A2:K2152	Expression	=$G2=Sheet2!$A$2	text	NO
J2:K2152	Expression	=$L2=0	text	YES
J2:K2152	Expression	=$L2=1	text	YES
J2:K2152	Expression	=$L2=2	text	YES
J2:K2152	Expression	=$L2=3	text	YES
J2:K2152	Expression	=$L2=4	text	YES
J2:K2152	Expression	=$L2=5	text	YES
G228:H447,A2:K294,A449:K2152	Expression	=AND(LEN($B2)>0,AND($B2=$B1,$B2=$B3))	text	YES
G228:H447,A2:K294,A449:K2152	Expression	=AND(LEN($B2)>0,$B2=$B3)	text	NO
G228:H447,A2:K294,A449:K2152	Expression	=AND(LEN($B2)>0,$B2=$B1)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
I1:I7	List	=Sheet3!$A$1:$A$18

Test.xlsx
	A	B	C	D
1				COLOR TABLE
2	-NO-	--- NO AUTO PLCMNT ---	NO AUTO PLCMNT	0
3	M100	M100 - IBSIP 0100 Math Placement	IBSIP 0100 Math Placement	5
4	M103	M103 - MTH 1030 Placement	MTH 1030 Placement	1
5	M120	M120 - CSTM 0120 Placement	CSTM 0120 Placement	5
6	M123	M123 - MTH 1023 Placement	MTH 1023 Placement	1
7	M203	M203 - MTH 2003 Placement	MTH 2003 Placement	1
Sheet3

 

[Fluff]

If you want the values in col L to overrule the other formats, then you will need to move them to the top of the list.

 

[psycoperl]

Duh! Ok. I think I need a vacation. Works fine now.

Now that I have the color scheme working - I want to simplify and not have to use the column L to determine the color and have the XLookup in Conditional Formatting. Reducing the chance of someone deleting the column or changing the formula accidentally.

When I change the Condition =$L2=1 to =XLOOKUP($I2,Sheet3!A$1:A$18,Sheet3!D$1:D$18,"",0,1)=1 only column J gets formatted, column K does not.

 

[Fluff]

It would need to be

=XLOOKUP($I2,Sheet3!$A$1:$A$18,Sheet3!$D$1:$D$18,"",0,1)=1

 

[psycoperl]

Ahh. Great! Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.