match names in 2 different columns

[Guest]

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

 

[Guest]

In C1 enter:
=IF(COUNTIF(A:A,"=" & B1)>0,B1,"")
and copy down

 

[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,"")

 

[Guest]

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,ROW(A1))),"",INDEX(B:B,MATCH(SMALL(D,ROW(A1)),D,0)))

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

Select C11, 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

 

[Guest]

The preceding play extracts in col C

.. names in col B which are within col A,
with all results neatly bunched at the top

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,ROW(A1))),"",INDEX(A:A,MATCH(SMALL(D,ROW(A1)),D,0)))

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

Select C11, 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 ..

---

 

[Guest]

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

Thanks again!

Mike

 

[Guest]

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

Great to hear that, Mike !
Thanks for the feedback ..

---