Conditional Formatting - Highlight when cell does not match a list of values

[hcwardlow]

Hello,

I've tried some solutions I found on other posts on this board but I just cannot get it to work on my spreadsheet!

In column E, I have a product name.
In column H, I have a client name.

I want all the product names for client A to be in blue, the product names for client b to be in pink, and the product names for client c to be in white. If there is a product for a different client (not a, b, or c) I want it to be in green.

I have a list of the client names on a separate sheet titled key.

I have the first ones down:
=H3=Key!$A$1, format blue
=H3=Key!$A$2, format pink
For Client C I didn't do a formula since I want it to be white anyway.

It's the "other client" formatting I can't get. I've tried formatting green for:
=H3<>Key!$A$1:$A$3
=NOT(ISNUMBER(MATCH(H3,Key!$A$1:$A$3, 0)))
=OR(H3<>Key!$A$1:$A$3)
AND I've tried writing out all the client names in quotes, separated by commas, rather than referencing them to cells on the Key worksheet.

These are either showing as an invalid formula or turning my whole column green, even when the cell in column H has client a, b, or c in it.


Any help is appreciated! I'm fine with also using a macro, but I couldn't get that to work either.

 

[=ODIN=]

the OR might work if written like this: =OR(H3<>Key!$A$1,H3<>Key!$A$2,H3<>Key!$A$3)
this might work, although i thought you could not use a separate sheet as a range unless you use a named range: =ISERROR(MATCH(h3,Key!$A$1:$A$3,0))

 

[hcwardlow]

Thank you! The 2nd formula worked perfectly!