Compare data of two Excel Columns A & B, and show data of Column A that do not exist in B

Tim picture Tim · Apr 27, 2011 · Viewed 211.5k times · Source

I have an Excel file that has columns A and B, both have data that may or may not exist in other column, I'm only interested in the data of column A that do not exist in B. These Columns have same Header names. What formula can I use to show which items in column A aren't in B?

Answer

paulmorriss picture paulmorriss · Apr 27, 2011

Put this in C2 and copy down

=IF(ISNA(VLOOKUP(A2,$B$2:$B$65535,1,FALSE)),"not in B","")

Then if the value in A isn't in B the cell in column C will say "not in B".