Find matches with multiple criteria

[Annette L]

Hi everyone,

My question is going to be easy for someone to answer, but for the life of me I can't get anything to work.

I have two lists with first name and last names in separate columns. I need to compare the lists and find out is the person is on both lists.

I have been using the =isnumber(match(_,_,)) function successfully with one column, but how do I do it for both columns?

Last	First
Smith	Sarah
Thompson	Nichole
Jones	Chris
Cain	Chris

LastName	FirstName
Smith	Tom
Jones	Chris
Miller	Sue
Phillips	Julie

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F
1	Last	First		LastName	FirstName	In Both
2	Smith	Sarah		Smith	Tom	FALSE
3	Thompson	Nichole		Jones	Chris	TRUE
4	Jones	Chris		Miller	Sue	FALSE
5	Cain	Chris		Phillips	Julie	FALSE
6
Lists

Cell Formulas
Range	Formula
F2:F5	F2	=ISNUMBER(MATCH(D2&"|"&E2,$A$2:$A$9&"|"&$B$2:$B$9,0))

 

[Annette L]

So I recreated what you suggested and I still get False for Chris Jones. Can't seem to find what I did wrong. Can you see it?

 

[Fluff]

Are you using this in 365 or 2016?

 

[Annette L]

Are you using this in 365 or 2016?

365

 

[Fluff]

In that case check that there are no leading/trailing spaces in any of the cells.