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

Question

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?

What have you tried so far? You are surely aware that you need basically a nested loop: in the outer loop you go through all cell of the Performers, take the value and check in the inner loop all values of "All Names" whether it matches - it's all! — Leo Chapiro, May 28 '14 at 09:31
Yes, I am aware of this and have done similar things; I have successfully located the values within the range using various functions, such as `VLOOKUP`, `MATCH`, and other similar functions, but I cannot get it to highlight the value within `All Names`. I have tried using a formula in Conditional Formatting whereby if any value in `All Names` matches any value in `Performers` then it is to be highlighted yellow, but this doesn't function correctly and highlights the incorrect cells. It's the part where it highlights the value in `All Names` yellow that is problematic. — Mus, May 28 '14 at 09:33
Believe me, if I could I would! It is highly confidential data, so I cannot even provide a glimpse of it I'm afraid. However, I have just added in some dummy data which is a very basic version of how my data appears. — Mus, May 28 '14 at 09:36

score 2 · Answer 1 · answered May 28 '14 at 10:42

2

Select ColumnA and use:

=MATCH(A1,B:B,0)>1

thus:

SO23907648 example

for a slightly simpler formula. The main difference with accepted A probably being that since such formatting is Conditional - IF is not normally necessary..

answered May 28 '14 at 10:42

pnuts

58,317
11
87
139

1

Indeed - a simpler solution! – Mus May 28 '14 at 11:46

score 1 · Accepted Answer · edited May 28 '14 at 10:56

1

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"

edited May 28 '14 at 10:56

Jean-François Corbett

37,420
30
139
188

answered May 28 '14 at 09:42

user3683046

26
1

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

2 Answers2