Match 2 columns to 2 columns in different worksheets
 

Worksheet1
I have a list of surnames in column A and a list of first names in column B.

Worksheet2
I have a list of surnames in column A and a list of first names in column B.

How do I show if there is any matches in BOTH the surnames and first names
in both worksheets i.e. it displays MATCH in a column.

I have tried using VLOOKUP tables but have been unsuccessful.

Match 2 columns to 2 columns in different worksheets
 

In summary this is what I need as below

WORKSHEET 1
A B
Surname First name
1 SMITH JOHN
2 JONES JANE
3 ADAMS ALAN
4 PETERS JULIE

WORKSHEET 2
A B C
Surname First name Match
1 SMITH ALAN NO
2 JONES JANE YES
3 ADAMS JOHN NO
4 RICE JULIE NO

Both the surname and first name must match across the worksheets.

"Mally" wrote:

Worksheet1
I have a list of surnames in column A and a list of first names in column B.

Worksheet2
I have a list of surnames in column A and a list of first names in column B.

How do I show if there is any matches in BOTH the surnames and first names
in both worksheets i.e. it displays MATCH in a column.

I have tried using VLOOKUP tables but have been unsuccessful.

Match 2 columns to 2 columns in different worksheets
 

Also there could be different numbers of rows in the two worksheets.

e.g. 200 rows in worksheet 1 but 5000 in worksheet 2.

"Mally" wrote:

Worksheet1
I have a list of surnames in column A and a list of first names in column B.

Worksheet2
I have a list of surnames in column A and a list of first names in column B.

How do I show if there is any matches in BOTH the surnames and first names
in both worksheets i.e. it displays MATCH in a column.

I have tried using VLOOKUP tables but have been unsuccessful.

Match 2 columns to 2 columns in different worksheets
 

Somewhere on row 1 :
names matches (array formula, validate with CTRL+Shift+Enter) :
=IF(LARGE(IF(ISNA(MATCH(Sheet2!$A$1:$A$15,Sheet1!$ A$1:$A$15,0)),0,MATCH(Sheet2!$A$1:$A$15,Sheet1!$A$ 1:$A$15,0))*IF(ISNA(MATCH(Sheet2!$B$1:$B$15,Sheet1 !$B$1:$B$15,0)),0,1),ROW())0,INDEX(Sheet1!$A$1:$A $10,LARGE(IF(ISNA(MATCH(Sheet2!$A$1:$A$15,Sheet1!$ A$1:$A$15,0)),0,MATCH(Sheet2!$A$1:$A$15,Sheet1!$A$ 1:$A$15,0))*IF(ISNA(MATCH(Sheet2!$B$1:$B$15,Sheet1 !$B$1:$B$15,0)),0,1),ROW())),"")
first names matches on row1 too and array formula too :
=IF(LARGE(IF(ISNA(MATCH(Sheet2!$A$1:$A$15,Sheet1!$ A$1:$A$15,0)),0,MATCH(Sheet2!$A$1:$A$15,Sheet1!$A$ 1:$A$15,0))*IF(ISNA(MATCH(Sheet2!$B$1:$B$15,Sheet1 !$B$1:$B$15,0)),0,1),ROW())0,INDEX(Sheet1!$B$1:$B $10,LARGE(IF(ISNA(MATCH(Sheet2!$A$1:$A$15,Sheet1!$ A$1:$A$15,0)),0,MATCH(Sheet2!$A$1:$A$15,Sheet1!$A$ 1:$A$15,0))*IF(ISNA(MATCH(Sheet2!$B$1:$B$15,Sheet1 !$B$1:$B$15,0)),0,1),ROW())),"")
Drag down as needed
PS : I used A1:B15 ranges on both sheets. Adjust to your needs. You should
choose the greatest range.
PPS : There should be a simpler formula but I cannot figuree it out.
--
Regards.
Daniel
"Mally" a écrit dans le message de news:
...
Also there could be different numbers of rows in the two worksheets.

e.g. 200 rows in worksheet 1 but 5000 in worksheet 2.

"Mally" wrote:

Worksheet1
I have a list of surnames in column A and a list of first names in column
B.

Worksheet2
I have a list of surnames in column A and a list of first names in column
B.

How do I show if there is any matches in BOTH the surnames and first
names
in both worksheets i.e. it displays MATCH in a column.

I have tried using VLOOKUP tables but have been unsuccessful.

Match 2 columns to 2 columns in different worksheets
 

=if(isnumber(match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0)),"yes","no")

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

====
An alternative is to combine the names in one cell (say in column C) in the
second sheet with a unique string between the fields:

=a1&"..."&b1

Then look for a match against that helper column:

=if(isnumber(match(a1&"..."&b1,othersheet!c:c,0)), "yes","no")

(not an array formula)

Mally wrote:

In summary this is what I need as below

WORKSHEET 1
A B
Surname First name
1 SMITH JOHN
2 JONES JANE
3 ADAMS ALAN
4 PETERS JULIE

WORKSHEET 2
A B C
Surname First name Match
1 SMITH ALAN NO
2 JONES JANE YES
3 ADAMS JOHN NO
4 RICE JULIE NO

Both the surname and first name must match across the worksheets.

"Mally" wrote:

Worksheet1
I have a list of surnames in column A and a list of first names in column B.

Worksheet2
I have a list of surnames in column A and a list of first names in column B.

How do I show if there is any matches in BOTH the surnames and first names
in both worksheets i.e. it displays MATCH in a column.

I have tried using VLOOKUP tables but have been unsuccessful.

--

Dave Peterson

Match 2 columns to 2 columns in different worksheets
 

Somewhere on row 1 :
names matches (array formula, validate with CTRL+Shift+Enter) :
=IF(LARGE(IF(ISNA(MATCH(Sheet2!$A$1:$A$15,Sheet1!$ A$1:$A$15,0)),0,MATCH(Sheet2!$A$1:$A$15,Sheet1!$A$ 1:$A$15,0))*IF(ISNA(MATCH(Sheet2!$B$1:$B$15,Sheet1 !$B$1:$B$15,0)),0,1),ROW())0,INDEX(Sheet1!$A$1:$A $10,LARGE(IF(ISNA(MATCH(Sheet2!$A$1:$A$15,Sheet1!$ A$1:$A$15,0)),0,MATCH(Sheet2!$A$1:$A$15,Sheet1!$A$ 1:$A$15,0))*IF(ISNA(MATCH(Sheet2!$B$1:$B$15,Sheet1 !$B$1:$B$15,0)),0,1),ROW())),"")
first names matches on row1 too and array formula too :
=IF(LARGE(IF(ISNA(MATCH(Sheet2!$A$1:$A$15,Sheet1!$ A$1:$A$15,0)),0,MATCH(Sheet2!$A$1:$A$15,Sheet1!$A$ 1:$A$15,0))*IF(ISNA(MATCH(Sheet2!$B$1:$B$15,Sheet1 !$B$1:$B$15,0)),0,1),ROW())0,INDEX(Sheet1!$B$1:$B $10,LARGE(IF(ISNA(MATCH(Sheet2!$A$1:$A$15,Sheet1!$ A$1:$A$15,0)),0,MATCH(Sheet2!$A$1:$A$15,Sheet1!$A$ 1:$A$15,0))*IF(ISNA(MATCH(Sheet2!$B$1:$B$15,Sheet1 !$B$1:$B$15,0)),0,1),ROW())),"")
Drag down as needed
PS : I used A1:B15 ranges on both sheets. Adjust to your needs. You should
choose the greatest range.
PPS : There should be a simpler formula but I cannot figuree it out.
PPPS : Please, ignore if this is the second post. I have not seen the first
one.
--
Regards.
Daniel

Match 2 columns to 2 columns in different worksheets
 

Thank you all for your help. I'll try these as soon as I can

"Daniel.C" wrote:

Somewhere on row 1 :
names matches (array formula, validate with CTRL+Shift+Enter) :
=IF(LARGE(IF(ISNA(MATCH(Sheet2!$A$1:$A$15,Sheet1!$ A$1:$A$15,0)),0,MATCH(Sheet2!$A$1:$A$15,Sheet1!$A$ 1:$A$15,0))*IF(ISNA(MATCH(Sheet2!$B$1:$B$15,Sheet1 !$B$1:$B$15,0)),0,1),ROW())0,INDEX(Sheet1!$A$1:$A $10,LARGE(IF(ISNA(MATCH(Sheet2!$A$1:$A$15,Sheet1!$ A$1:$A$15,0)),0,MATCH(Sheet2!$A$1:$A$15,Sheet1!$A$ 1:$A$15,0))*IF(ISNA(MATCH(Sheet2!$B$1:$B$15,Sheet1 !$B$1:$B$15,0)),0,1),ROW())),"")
first names matches on row1 too and array formula too :
=IF(LARGE(IF(ISNA(MATCH(Sheet2!$A$1:$A$15,Sheet1!$ A$1:$A$15,0)),0,MATCH(Sheet2!$A$1:$A$15,Sheet1!$A$ 1:$A$15,0))*IF(ISNA(MATCH(Sheet2!$B$1:$B$15,Sheet1 !$B$1:$B$15,0)),0,1),ROW())0,INDEX(Sheet1!$B$1:$B $10,LARGE(IF(ISNA(MATCH(Sheet2!$A$1:$A$15,Sheet1!$ A$1:$A$15,0)),0,MATCH(Sheet2!$A$1:$A$15,Sheet1!$A$ 1:$A$15,0))*IF(ISNA(MATCH(Sheet2!$B$1:$B$15,Sheet1 !$B$1:$B$15,0)),0,1),ROW())),"")
Drag down as needed
PS : I used A1:B15 ranges on both sheets. Adjust to your needs. You should
choose the greatest range.
PPS : There should be a simpler formula but I cannot figuree it out.
PPPS : Please, ignore if this is the second post. I have not seen the first
one.
--
Regards.
Daniel