# 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))
``````

If you'd like to return the unfound value and return empty string for found values

``````=IF(ISNUMBER(MATCH(B1,ColumnToSearch,0)),"",B1)
``````
-
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
``````
-

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.

-

=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

-

Select the two columns. Go to Conditional Formatting and select Highlight Cell Rules. Select Duplicate values. When you get to the next step you can change it to unique values. I just did it and it worked for me.

-
if you have a value in column B repeated, this will not highlight them – magodiez Nov 13 '15 at 16:18

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.

-
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, "") – user374241 Oct 1 '14 at 17:48

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.

-

in `C1` write `=if(A1=B1 , 0, 1)`. Then in `Conditional formatting`, select `Data bars` or `Color scales`. It's the easiest way.

-

## protected by NifleMay 12 '15 at 16:26

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site.