Conditional formatting based on multiple columns

[detriez]

I am trying to format a cell when the value in a matched adjacent cell =100

I need to apply conditional format to Column A
DEF (Cell A3) and JKL (cell A5) are formatted because the thier score in Column H =100

If A.value = G.value and H.value = 100
Then Set Formula

 

[detriez]

I've got the first part working

=MATCH(A2,G2:G5,0)

Now I need to add to this..... AND Score=100

 

[Alex Blakenburg]

Try this. Use a lookup function (I used Vlookup), if found and 100 return true otherwise return false.
You don't need column B that was just my workings before moving the formula into the conditional formatting box.

20210318 Conditional Formatting with Vlookup.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Data								Value	Score
2	ABC	FALSE							XYZ	100
3	DEF	FALSE							DEF	200
4	GWF	FALSE							second	300
5	JKL	FALSE							JKL	100
6
Finished

Cell Formulas
Range	Formula
B2:B5	B2	=IFERROR(VLOOKUP(A2,$I$17:$J$20,2,FALSE)=100,FALSE)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A5	Expression	=IFERROR(VLOOKUP(A2,$I$2:$J$5,2,FALSE)=100,FALSE)	text	NO

 

[Alex Blakenburg]

If you want to make it smarter.
1) Put the 100 in a cell and give it a meaningful rangename, this will mean you can change the 100 to something else should you need to.

2) convert the score table into an Excel Table and give it a meaningful name.
This will mean it will expand and contract with the number of rows in the table so you don't have to modify the conditional formatting formula.
In the below I have changed the table name from Table1 to tbl_Score

20210318 Conditional Formatting with Vlookup.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Data								Value	Score
2	ABC	FALSE							XYZ	100
3	DEF	FALSE							DEF	200
4	GWF	FALSE							second	300
5	JKL	TRUE							JKL	100
6
With Table

Cell Formulas
Range	Formula
B2:B5	B2	=IFERROR(VLOOKUP(A2,tbl_Score,2,FALSE)=100,FALSE)

Named Ranges
Name	Refers To	Cells
tbl_Score	=Table1[#All]	B2:B5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A5	Expression	=IFERROR(VLOOKUP(A2,tbl_Score,2,FALSE)=100,FALSE)	text	NO

 

[detriez]

Brilliant Alex.. lookup never occured to me.
Works exactly as expected

Thanks, I appreciate your help

 

[Alex Blakenburg]

Not often I get instant feedback, Love it.