Microsoft Excel - comparing values of multiple columns against values of multiple other columns

Question

I have a spreadsheet containing names.

I have 5 columns of unique names, let's call the headers:

Name A
Name B
Name C
Name D
Name E

I also have 10 columns of other names (not necessarily unique), let's call the headers:

Name 1
Name 2
Name 3
etc.

What I would like to do is compare the value in Name 1 to Name 10 against the values in Name A to Name E and highlight (using conditional formatting perhaps) any values in Name 1 to Name 10 that DO NOT appear in any of the columns Name A to Name E.

If anyone has any ideas on how to do this with conditional formatting (or by any other means) I would be extremely grateful!

Answer 1

I rewrote the answer completely after seeing your sample (you need to work more on how to express your problem),

First, I'm posting the request image as is, so people would understand the problem:

You can achieve that using two formulas:

For cells in G column (Name 1) use the following:

=AND(NOT(ISBLANK(G2)),ISERROR(MATCH(G2,$A2:$E2,0)))

For the other columns, use this:

=AND(NOT(ISBLANK(H2)),ISERROR(MATCH(H2,$A2:$E2,0)),ISERROR(MATCH(H2,$G2:G2,0)))

Answer 2

If you wish to avoid using a lengthy formula, Excel conditional formatting has an existing rule you can use. It may be too simplistic for what you are trying to do, but if you highlight all the data you can select conditional formatting and choose the rule "Format only unique values". Anything highlighted in the second set of data would not appear in the first set of data. It will not work though for any duplicate values in the second set of data.