match names in 2 different columns

M

Mike

Hey guys, how can I match names in two different columns and kick out the
matches in a 3rd column?

For example

bill john john
mary mike mike
mike david
john sam


any ideas?

Thanks!

Mike
 
D

Don Guillett

this formula will do it but if you don't want blanks you will need a macro
=IF(COUNTIF($J$1:$K$4,J1)>1,J1,"")
 
M

Max

Another play which dredges the results out neatly at the top ..

Assuming names listed in cols A & B, from row1 down

Put in C1:
=IF(ISERROR(SMALL(D:D,ROW(A1))),"",INDEX(B:B,MATCH(SMALL(D:D,ROW(A1)),D:D,0)))

Put in D1:
=IF(B1="","",IF(ISNUMBER(MATCH(B1,A:A,0)),ROW(),""))

Select C1:D1, fill down to last row of data in col B

Col C will auto-extract the names in col B which are within col A,
with all results neatly bunched at the top
 
M

Max

The preceding play extracts in col C
.. names in col B which are within col A,
with all results neatly bunched at the top
Click to expand...

If you need to (conversely) match it the other way around
(i.e. names in col A with those within col B),
just similarly ..

Place in C1:
=IF(ISERROR(SMALL(D:D,ROW(A1))),"",INDEX(A:A,MATCH(SMALL(D:D,ROW(A1)),D:D,0)))

In D1:
=IF(A1="","",IF(ISNUMBER(MATCH(A1,B:B,0)),ROW(),""))

Select C1:D1, fill down to last row of data in col A

Col C will auto-extract the names in col A which are within col B,
with all results neatly bunched at the top ..
 
M

Mike

Excellent!! Thanks guys! I tried it both ways! Did exactly what I needed!

Thanks again!

Mike
 
Top