asked on 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.
Microsoft ExcelMicrosoft Office
Last Comment
Ryan Chong
ASKER
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",""))
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Open in new window
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.