Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

marked as duplicate by Ellesa, Joe Taylor, Diogo, Renan, 8088 Jul 30 '12 at 14:25

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1  
@David Vandenbos has provided one answer that works, or do you need to have the list displayed or moved to a new location? –  datatoo Jul 28 '12 at 18:08

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.