# Find items in one Excel column that are not in another column and put them in a new column

What I want to do

Find Items in one column (`ColA`) that are not in another column (`ColB`). What to do when I want the result not being highlighted, but have it in another column (`ColC`) without blank rows.

Example

``````ColA - ColB - ColC
1 - 1 - 4
3 - 2 - 8
10 - 3 - 10
4 - 5 - ""
5 - 7 - ""
8 - 6 - ""
9 - 9 - ""
``````

What did I try yet

Until now I succeeded to have the following result. I do this with the following formula in column C:

``````=IF(IFERROR(MATCH(A2;B\$2:F\$300;0);"")<>"";"";A2)
``````

Result:

``````ColA - ColB - ColC
1 - 1 - ""
3 - 2 - ""
10 - 3 - 10
4 - 5 - 4
5 - 7 - ""
8 - 6 - 8
9 - 9 - ""
``````

But I want to avoid the blank cells in Col C.

I tried the formula I found in one of the answers on this site:

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

… but this does not work. Maybe I adapt this formula in a wrong way...?

-

``````=IFERROR(SMALL(IF(ISNA(MATCH(A1:A10,B1:B10,0)),A1:A10,""),ROW()),"")
Store it as an array formula by pressing Ctrl+Shift+Enter (on Windows) / Cmd+Shift+Enter (on OS X) – if you did it right, the formula will display enclosed in braces (`{}`) and look identical in all cells. As applied to your example data, this is what you get: