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
|