excellookuphighlightconditional-formattingexcel-match

If Equal to Value in Other Range, Then Highlight Value in Range

I have two columns of data - a reference column All Names and another list Performer of names that meet a certain criterion (the criterion is not relevant here).

All Names contains 2,029 names, Performer contains ~120 names. They are not matched up (i.e. Bill Smith does not sit beside Bill Smith, but rather beside Jessica Hart).

What I want to do is to check each name in Performer against the list All Names and, if it exists within All Names, I then want to highlight the value in All Names so that I can easily identify it.

For example, if Bill Smith (which resides in Performers) also exists within All Names, then I want to highlight the value Bill Smith in All Names for easy visual reference.

Here is an example of how my data appears:

ALL NAMES         |PERFORMER
------------------|--------------
Bill Smith        |       
Jane Smith        |       
Vikram Gujeravi   |Enoch Thistle
Sebastian Davies  |Nicole Dunning
Enoch Thistle     |
Nicole Dunning    |Bill Smith

This should result in the names of Enoch, Nicole and Bill being highlighted in the All Names column.

Example:

IF `Performers`("BILL SMITH") exists within `All Names`
THEN highlight `All Names`("BILL SMITH") yellow

How can this be achieved?

Solution

  • you can use a conditional formatting on the first column:

    =IF(VLOOKUP(A2,$B$2:$B$17,1,FALSE)>0,1,0)

    A2 is the location of the first "all names" and B2:B17 is the range of "performer"