vlookup, i never seem to get it right.

B

brodiemac

I have a list of people in column A and another in column B. Column A is
longer than column B so I want to extract what names are missing from column
B into column C. I don't know why I can't seem to get vlookup to work for
this so I'm asking for some help.

HELP!!!!
 
A

Ardus Petus

=ISERROR(MATCH(A1,B$1:B$999,0))
and drag down to check all cells of column A
Those missing will be marked TRUE

HTH
 
M

Max

brodiemac said:
I have a list of people in column A and another in column B. Column A is
longer than column B so I want to extract what names are missing from column
B into column C. I don't know why I can't seem to get vlookup to work for
this so I'm asking for some help.
Click to expand...

Another play which can extract the required list directly into col C ..

Assume data in cols A and B start in row2 down

In C2:
=IF(ROW(A1)>COUNT(D:D),"",INDEX(A:A,MATCH(SMALL(D:D,ROW(A1)),D:D,0)))

In D2:
=IF(A2="","",IF(ISNUMBER(MATCH(A2,B:B,0)),"",ROW()))
(Leave D1 empty)

Select C2:D2, copy down to the last row of data in col A

Col C will return the required results, ie the names in col A not found in
col B, with all results neatly bunched at the top
 
Top