We help IT Professionals succeed at work.

Compare Excel Spreadsheet.

98 Views
Last Modified: 2017-04-07
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.
Comment
Watch Question

CERTIFIED EXPERT

Commented:
in Spreadsheet 2, cell c2, try use formula:

=IF(ISERROR( MATCH(B2 & ", " &A2,Sheet1!A:A,0) ), "", "Match")

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.

Author

Commented:
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
Subodh Tiwari (Neeraj)Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015

Commented:
You may try something like this.
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",""))

Open in new window

Adjust the ranges as required.
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION