Conditional Formatting Based on 2 Criteria

[Legacy 143009]

I am quite weak at conditional formatting.
What should I do to paint column B cell values in red if column A value equals to matches any value in column C and the adjacent D value is smaller than column C value?

Column A	Column B	Column C	Column D
Cat	25	Cat	23
Bird	4	Dog	10
		Bird	5

 

[Legacy 143009]

and the adjacent D value is smaller than column C value?

"...and the adjacent D value is smaller than column B value?"

 

[Craggs82]

i used to hate conditional formatting, then i realised i could work out the needed formula in a cell, which is a much more user friendly interface, and then just paste it in.

=IFERROR(XLOOKUP(@A:A,C:C,D:D)<@B:B,FALSE)

uses an xlookup to return the column D value based on column A, then compares it to B. if its not found it will N/A error, which will result in a false return.

 

[Legacy 143009]

i used to hate conditional formatting, then i realised i could work out the needed formula in a cell, which is a much more user friendly interface, and then just paste it in.

=IFERROR(XLOOKUP(@A:A,C:C,D:D)<@B:B,FALSE)

uses an xlookup to return the column D value based on column A, then compares it to B. if its not found it will N/A error, which will result in a false return.

Thanks for your reply!
I think my Excel version does not support XLOOKUP Also what does "@" sign mean? I've never used it before.

 

[Craggs82]

i'd miss the functionality of 2016+ if i lost it now! the @ sign is structured reference to mean "in this row".
We can bypass the xlookup using vlookup. xlookup is simply a replacement in more recent excel versions.

=IFERROR(VLOOKUP(A1,C:D,2,FALSE)<B1,FALSE)

if you apply that to the entire B column it should work in your version. its important that the A1 and B1 references are the first cell the conditional formatting is applied to.

 

[Legacy 143009]

I haven't tested yet but I understand what you mean. Thanks a lot!

 

[Peter_SSs]

Is it certain that the values in column C will never appear more than once in column C? That is, can we be sure that columns C:D would never be like this?

22 11 04.xlsm
	C	D
1	Cat	28
2	Dog	10
3	Bird	5
4	Cat	15
CF (2)

If the answer is that the column C values are unique, then a simpler version (no need for the IFERROR) of the suggested formula should also work for you

22 11 04.xlsm
	A	B	C	D
1	Cat	25	Cat	23
2	Bird	4	Dog	10
3	Rat	5	Bird	5
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1:B3	Expression	=VLOOKUP(A1,C:D,2,FALSE)<B1	text	NO

If it is possible that values can repeat in column C then a different formula would be required. One such would be this

22 11 04.xlsm
	A	B	C	D
1	Cat	25	Cat	28
2	Bird	4	Dog	10
3			Bird	5
4			Cat	15
CF (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1:B2	Expression	=COUNTIFS(C$1:C$9,A1,D$1:D$9,"<"&B1)	text	NO

 

[Legacy 143009]

Yes, column C is a unique list. Yes, I stripped IFERROR function. Thanks for the reminder anyway
I am good at formulas but I always have confusions while I build up a condition. I am never certain when to choose a range and when to choose a value. Sometimes I select a range to apply and Conditional Formmatting is givin an error something like "Can not select a range" or something similar. I know functions, I know arrays, I know VBA but never understand Conditional Formatting