Matching two pairs of cells between two lists

[pierre86]

Hello,

I have a long master list of registered members,
column C has last name, column D has join date.

Now I have a short list of last names with join dates.

I want to compare the short list with the master list to find names that are already there, by comparing the last name and join date.



Tks
Pierre

[jeffreybrown]

Hi Pierre & Welcome to the Forum,

Here is one of the easiest ways with using concatenation...

E2 copied down >> =C2&D2
F2 copied down >> =MATCH(E2,$J$2:$J$3,0)
J2 copied down >> =H2&I2

In Column F, the number represents the ordinal position in which a match is found from the range J2:J3

	C	D	E	F	G	H	I	J
1	Name	Join Date	Combined	Match		Name	Join Date	Combined
2	Jack	9/13/2012	Jack41165	1		Jack	9/13/2012	Jack41165
3	Jill	9/14/2012	Jill41166	#N/A		John	9/15/2012	John41167
4	John	9/15/2012	John41167	2

[pierre86]

Never mind, I bad, had the range =MATCH(E2,$J$2:$J$3,0) set up wrong.
..........................................s/b =MATCH(E2,$J$2:$J$37,0)

Thank you, your solution is elegant by it's simplicity.

How ever, I don't know why but of the 12 short list records, 2 are flagged as matched, but 4 other that should show matched are not.

the Green background are ok, the yellow background does not work for some reason.

I have attached the sample file.

Thanks again
Pierre

[jeffreybrown]

Looks like you need to expand the match range...

=MATCH(E2,$J$2:$J$13,0)

[Haseeb Avarakkan]

Try in F2, then copy down.

=COUNTIFS(H$2:H$13,C2,I$2:I$13,D2)>0

TRUE means, matched.

[pierre86]

Thank you, Haseeb, works well and looks good too, and Jeffrey you are great too!!!

:-D

[jeffreybrown]

You're welcome…glad you have a workable solution and thanks for the feedback...

Haseeb is indeed light years ahead of me with the formulas...Thanks Haseeb

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[pierre86]

For whom might be reading this in the future, if you apply
=MATCH(E2,$J$2:$J$13,0) to the main list
then to the secondary list you reverse the "E" and "J" in the formula
=MATCH(J2,$E$2:$E$13,0)

You then get the matching list of names to line up. ;-)

See attached
Pierre

[pierre86]

here is the file