Excel Conditional Formatting Two lists on differen

Question

I have two lists, List A and List B, on two different spreadsheets, that I need to compare and format.

List A is a reference list with two columns, Name and Type.
List B contains only one column, Name, and may contain some of the names in List A and not necessarily in that order.

With conditional formatting I want to use List A to format List B's names depending on the type specified for the name in the Type column in List A.

I have tried with some different formulas but not got it to work.

Let me know if something is not clear in my description of the problem!

Grateful for any help!

Answer 1

Suppose if your list A are same worksheet as list B, will be easier.

For your case you need to name List A as below (I named it as List):

Then go to List B Select the Range you want to apply Conditional Formatting. Then Add 3 rules as below:

The formula are like below :

The result will like below :

Hope this help!