Excel: highlight cell if exists in list

2
votes

I want to create a conditional formatting rule where the cell will be highlighted if it also appears in a list (column A).

The values are all text (e.g "Apple", "Pear"). It has to be an exact match ("Apple Juice" shouldn't be highlighted if "Apple" is in the list). The result should be this: Image of spreadsheet

Help will be greatly appreciated!

2
excelexcel-formulaconditional-formatting
I see you are new to SO. It's customary SO etiquette to accept an answer if you feel it solves your problem. - JGFMK

2 Answers

3
votes

Select cell b2 in your example

Then from Home Ribbon

Conditional Formatting> New Rule > Classic > Use a formula to determine which cells to format

Then in formula enter:

=countif($A2:$A6,b2)>0

After you've done that use format paint brush on remaining b3 and b4 in your image

0
votes

I am using the match function. So in your example it would be like this:

=MATCH($A2,$B2:$B4,0)

and then use the Format Painter for the rest of the cells you want to apply it.