The other day I had to create a "diff" between two lists in excel. More exactly, I had to verify which emails were missing between two lists of emails.

Solutions

I could find two solutions to this problem, which are listed below. For both, I'll assume that columns A and B contain the two lists and the formula is placed in column C.

Nice way

A more dedicated way is to take the element in B and check it against column A via a lookup. A formula like:

=IF(LOOKUP(B1,A:A)=B2, "OK", "")

will give "OK" if there's a match.

This solution does not require that lists are ordered.

Quicker solution

An alternative solution is with a dedicated MATCH() function:

=MATCH(B1,A:A,0)

This will give the row number if there's a match and a N/A if there isn't.

This solution needs the columns to be ordered.

HTH,