Compare Excel Spreadsheet.
Dear Excel pros, I need some help please!
I have two Excel Spreadsheets.
Spreadsheet 1 contains list of username like the following in one column:
Employee Names
Burris, Jack
Cortez, Eric
Smith, Mary
Brand, John
Boamah, Nana Yaa
Spreadsheet 2 contains list of First Names and Last Names in separate columns:
First Name Last Name
Jack Burris
John Wang
Ed Cool
Mary Smith
Jack Casey
Boamah Nana Yaa
I want to have spreadsheet 2 to look up spreadsheet 1 and put "Match" in 3rd column next to the names that matched (Both Firstname and Lastname appear in sheet 1). For example:
First Name Last Name
Jack Burris Match
John Wang
Ed Cool
Mary Smith Match
Jack Casey
Boamah Nana Yaa Match
Please advise. Thanks.
I have two Excel Spreadsheets.
Spreadsheet 1 contains list of username like the following in one column:
Employee Names
Burris, Jack
Cortez, Eric
Smith, Mary
Brand, John
Boamah, Nana Yaa
Spreadsheet 2 contains list of First Names and Last Names in separate columns:
First Name Last Name
Jack Burris
John Wang
Ed Cool
Mary Smith
Jack Casey
Boamah Nana Yaa
I want to have spreadsheet 2 to look up spreadsheet 1 and put "Match" in 3rd column next to the names that matched (Both Firstname and Lastname appear in sheet 1). For example:
First Name Last Name
Jack Burris Match
John Wang
Ed Cool
Mary Smith Match
Jack Casey
Boamah Nana Yaa Match
Please advise. Thanks.
CERTIFIED EXPERT
in Spreadsheet 2, cell c2, try use formula:
apply this by drop it down to following rows.
pls note for last entry:
Boamah Nana Yaa
will not be matched as the Last Name and FirstName is in reverse order.
=IF(ISERROR( MATCH(B2 & ", " &A2,Sheet1!A:A,0) ), "", "Match")
apply this by drop it down to following rows.
pls note for last entry:
Boamah Nana Yaa
will not be matched as the Last Name and FirstName is in reverse order.
Thanks. I will test. In sheet 2, the user name is Nana Yaa Boamah. It should show a match, right?
Also, if I have the following in sheet 1 (no comma), will your formula work?
Burris Jack
Cortez Eric
Smith Mary
Brand John
Boamah Nana Yaa
Also, if I have the following in sheet 1 (no comma), will your formula work?
Burris Jack
Cortez Eric
Smith Mary
Brand John
Boamah Nana Yaa
You may try something like this.
The following formula will also work if on the first sheet, names are not separated by a comma.
The following formula will also work if on the first sheet, names are not separated by a comma.
=IF(ISNUMBER(MATCH(B2&" "&A2,INDEX(SUBSTITUTE(Sheet1!$A$2:$A$1000,",","")&SUBSTITUTE(Sheet1!$B$2:$B$1000,",",""),),0)),"Match",IF(ISNUMBER(MATCH(A2&" "&B2,INDEX(SUBSTITUTE(Sheet1!$A$2:$A$1000,",","")&SUBSTITUTE(Sheet1!$B$2:$B$1000,",",""),),0)),"Match",""))