Compare Excel spreadsheet - 1

nav2567
nav2567 used Ask the Experts™
on
I have 2 spreadsheets in the same workbook.

In spreadsheet 1, column B and C, I have list of user First and Last Names:

First Name      Last Name
John            Doe
Mary            Dawson
Rob                    Cool
Ivan            Dyals


In spreadsheet 2, column K and L, I have list of user First and Last names:

First Name      Last Name--->this is in row 2
Mary            Dawson
Anthony            Fusco
Jack                    Palermo
Rob                    Cool
....

I want to column M of spreadsheet 2 to show MATCH if name is found in spreadsheet 1:

First Name      Last Name
Mary            Dawson            MATCH
Anthony            Fusco            
Jack                    Palermo            
Rob                    Cool            MATCH
....

I am using the below similar to what another expert suggests in M3 of spreadsheet 2 but it is not working.  

=IF(ISERROR( MATCH(L3 & ", " &K3,Sheet1!F:F,0) ), "", "Match")

Please advise in this ticket.  Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
The formula you have shown doesn't match the column references you described e.g. column F.

Try this...
On Sheet2
In M2
=IF(ISNUMBER(MATCH(K2&L2,INDEX(Sheet1!$B$2:$B$100&Sheet1!$C$2:$C$100,),0)),"Match","")

Open in new window

change the range references used in the formula as per your requirement. But don't use the full column reference in the formula.

Author

Commented:
Sorry, it is not working.  The entire column M shows TRUE.
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
Hi,

Try below:
=IF(OR(COUNTIF(K:K,K2)=COUNTIF(Sheet1!B:B,K2),COUNTIF(L:L,L2)=COUNTIF(Sheet1!C:C,L2)),"Match","")

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial