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?
Excel – How to Find Items in One Column Not in Another
conditional formattingmicrosoft excelmicrosoft-excel-2007
Related Solutions
You will have to use an array formula. Assuming your data in ColA and ColB is in rows 1 to 10, select rows 1 to 10 of ColC, then input the following formula in the formula bar:
=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:
Note there are a few gotchas when working with array formulas (they can only be edited by editing the whole range, cannot be shrunk, can only be expanded by manually editing the formula … see the article I linked to for some details, section “Rules for entering and changing array formulas”).
You could use VLOOKUP to initially populate the data, then copy values onto a new sheet to remove the reference.
Best Answer
=MATCH(B1,ColumnToSearch,0)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 you'd like to return the unfound value and return empty string for found values