Finding non duplicates

Question

I have 2 lists of names:

ListOfNames1	ListOfNames2
James	James
John	John
Robert	Robert
Michael

I want to make a formula that will detect when a name did not find a match on the other list and output that name, so in this case it would output Michael.

There is probably a very simple solution to this but I simply cannot find it. I tried using stuff such as UNIQUE(), FILTER() but with no luck.

The demo sheet: https://docs.google.com/spreadsheets/d/1uqN6pB7xRXzWmDe1LPWUmo4HNeuH5PTtf2Ckb1B6dvg/edit?usp=sharing

Answer 1

As an alternative to the solution already provided, you can also try this:

=FILTER(A2:AA,ISNA(MATCH(A2:A,B2:B,0)))

The above formula uses the following functions:

• MATCH - in order to return the unique value;

• ISNA - for checking whether the match is the error #N/A;

• FILTER - for returning the desired result;

Reference

• MATCH function;

• ISNA function;

• FILTER function.

Answer 2

use:

=FILTER(A2:A, NOT(COUNTIF(B2:B, A2:A)))