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

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?

-

1. Select the list in column A
2. Right-Click and select Name a Range...
3. Enter "ColumnToSearch"
4. Click cell C1
5. Enter this formula: `=MATCH(B1,ColumnToSearch,0)`
6. Drag the formula down for all items in B

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:

``````=ISNUMBER(MATCH(B1,ColumnToSearch,0))
``````
-
Then... Put this formula into Conditional Formatting in both lists and and use it to highlight (or whatever) the non-matching cells –  Kije Dec 10 '09 at 19:55

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

-

=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)),"")

Comparing two columns of names and returning missing names

-

Took me forever to figure this out but it's very simple. Assuming data begins in A2 and B2 (for headers) enter this formula in C2:

``````=MATCH(B2,\$A\$2:\$A\$287,0)
``````

Then click and drag down.

A cell with `#N/A` means that the value directly next to it in column B does not show up anywhere in the entire column A.

Please note that you need to change \$A\$287 to match your entire search array in Column A. For instance if your data in column A goes down for 1000 entries it should be \$A\$1000.

-

My requirements was not to highlight but to show all values except that are duplicates amongst 2 columns. I took help of @brenton's solution and further improved to show the values so that I can use the data directly:

``````=IF(ISNA(MATCH(B2,\$A\$2:\$A\$2642,0)), A2, "")
``````

Copy this in the first cell of the 3rd column and apply the formula through out the column so that it will list all items from column B there are not listed in column A.

-

Thank you to those who have shared their answers. Because of your solutions, I was able to make my way to my own.

In my version of this question, I had two columns to compare -- a full graduating class (Col A) and a subset of that graduating class (Col B). I wanted to be able to highlight in the full graduating class those students who were members of the subset.

I put the following formula into a third column:

``````=if(A2=LOOKUP(A2,\$B\$2:\$B\$91),1100,0)
``````

This coded most of my students, though it yielded some errors in the first few rows of data.

-

There is an error in the formula above

=IF(ISNA(MATCH(B2,\$A\$2:\$A\$2642,0)), A2, "")

should be

=IF(ISNA(MATCH(B2,\$A\$2:\$A\$2642,0)), B2, "")

-
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient reputation you will be able to comment on any post. –  Rhys Gibson Oct 2 at 2:07