conditional formatting from a list

[casey]

How do I conditionally format a cell if the data in the cell matches any cell
in a list of 100?

For example:

Col A Col B
bob art
jim bob
kirk will
greg sam

I want to format the cells in Col A such that "bob" will fill red with white
letters if his name shows up in Col B

 

[Conan Kelly]

casey,

1. Select the cells in Column A that you want to have this conditional
formatting.
2. Click Format > Conditional Formatting...
3. Change "Cell Value Is" to "Formula Is"
4. Enter the following formula (using your example posted: Range B1:B4):
=not(isna(vlookup(A1,$B$1:$B$4,1,0)))
(that formula assumes that in your selection (A1:A4), cell A1 is the active
cell)
5. Set your formatting accordingly.

HTH,

Conan

 

[casey]

Works perfectly, Conan. Thank you.

And since you answered so well, I have a follow-up question.

What if I have another condition such as:

Col A Col B Col C Col D
Row 1 family_b bob family_b art
Row 2 jim family_b bob
Row 3 kirk family_d will
Row 4 greg family_e sam

1) I would like to turn "bob" in Col B red if $A$1 (family_b) matches Col C
AND if "bob" in Col B (B1) matches Col D.

2) If only "bob" in Col B matches Col D, then it turns "bob" in Col B orange.