Highlight cell if value is present in any cell in another column

google-sheets gs-conditional-formatting google-sheets-formula
azizLIGHT picture azizLIGHT · Mar 14, 2015 · Viewed 22k times · Source

I want to highlight the cell in column A if it is repeated anywhere in column B. For example:

A      | B
pack_1 | unrelated
pack_2 | unrelated
pack_3 | pack_1
pack_4 | pack_1
pack_5 | pack_3
pack_6 | pack_3
pack_7 | unrelated
pack_8 | pack_2

In the example, pack_1, pack_2 and pack_3 should be highlighted because they are mentioned in column B.

How can I do such a thing?

Answer

furman87 picture furman87 · Mar 14, 2015

Do conditional formatting on each cell with the formula:

=EQ(VLOOKUP(A1, B:B, 1, FALSE), A1)

And format the cell to a different color if it matches.

I shared an example here:

https://docs.google.com/spreadsheets/d/1IovLko1cF2guKnIalCyE0uSbCvMDYLgL0BZHt35znXI/edit?usp=sharing

Related questions

Conditional formatting based on another cell's value

I'm using Google Sheets for a daily dashboard. What I need is to change the background color of cell B5 based on the value of another cell - C5. If C5 is greater than 80% then the background color is green …

Read More
How to highlight cell if value duplicate in same column for google spreadsheet?

I am looking for formula for google spreadsheet highlight cell if value duplicate in same column can anyone please assist me for this query?

Read More
Script to Change Row Color when a cell changes text

I have a Google spreadsheet where I keep a list of bugs and whenever I fix a bug I change the status from "Not Started" to "Complete". I want to write a script for the Google Docs spreadsheet such that …

Read More