Excel: Find Items in one column that are not in another column

Question

I have two columns in excel, and I want to find (preferably highlight) the items that are in column B, but not in column A.

What's the quickest way to do this?

DanM · Answer 1 · 2009-12-10 19:01:46Z

If the formula fails to find a match, it will be marked #N/A, otherwise it will be a number.

If you'd like it to be TRUE for match and FALSE for no match, use this formula instead:

=IF(ISNA(MATCH(B1,ColumnToSearch,0)),FALSE,TRUE)

Then... Put this formula into Conditional Formatting in both lists and and use it to highlight (or whatever) the non-matching cells

Kaze · Answer 2 · 2011-05-09 16:18:08Z

Here's a quick-and-dirty method.

Highlight Column B and open Conditional Formatting.

Pick Use a formula to determine which cells to highlight.

Enter the following formula then set your preferred format.

=countif(A:A,B1)=0

C. Ross · Answer 3 · 2011-10-21 14:50:15Z

See my array formula answer to listing A not found in B here:

=IFERROR(INDEX($A$2:$A$1999,MATCH(0,IFERROR(MATCH($A$2:$A$1999,$B$2:$B$399,0),COUNTIF($C$1:$C1,$A$2:$A$1999)),0)),"")

3 Answers