Excel – In Excel how can I return all the items that are not found in an original list (based on a new list)?

microsoft excelmicrosoft-excel-2010

Possible Duplicate:
Extract values from a range that are missing from another

I have two columns in Excel (Column A and B , going left to right), like so:

Adel    Adel
Adam    Bob
Ashley  chad
chad    mike
        morris
        muhsin

And I'd like to get all the items from Column B that are not in Column A: i.e., Bob, Mike, Morris, Muhsin.
I know I need VLOOKUP() , however I'm a bit lost on how to do it.

I appreciate any tips or advice.

Best Answer

A quick way to accomplish this does use VLOOKUP. Here's how I did it:

  1. Create a table from your values (not necessary, but makes language and updating very easy). I told Excel I didn't have header columns, so it assigned the generic ones-IMO its worth you spending the time to properly set up column names.

  2. In column C enter the formula:

    =IFERROR(VLOOKUP([@Column2],[Column1],1,FALSE),"Missing")

  3. To find the values in Column 2 that are missing from Column 1, filter the table on Column 3 for the "Missing" value.

Missing Values

This works by using VLOOKUP to search Column 1 for the value in Column 2, looking for an exact match. Since we're looking for missing values, VLOOKUP will return an NA# error when it can't find the values (which is what we're looking for). So, the IFERROR will return the string "Missing" whenever Excel can't find a match. Filter those values and your table will do the work for you. You could also make an additional column with the lookup values reversed and return the values that are in Column 1, but not Column 2.

Related Question