Match First + Last Name, then compare Salary

Copper Contributor

Hello! I am wondering how I can first match first and last names across two different worksheets, then compare their salaries and have the column say true/false if they do not match. 

Example:

Worksheet 1

Emily Doe $2000

JohnDoe$1000

 

Worksheet 2

Emily Doe $2500 False

JohnDoe$1000True

So I would first have to match the first and last name

Then I would have to compare the salary

Since Emily's is $2000 in W1 and $2500 in W2, she would come out as False (since they do not match)

Since John's matches, his would turn out True

Can anyone help? Thank you!!

2 Replies

@hello12324343232131 

Assuming that there are no duplicate combinations of first name/last name:

In D2 on Worksheet 2:

 

=SUMIFS('Worksheet 1'!$C$2:$C$1000, 'Worksheet 1'!$A$2:$A$1000, $A2, 'Worksheet 1'!$B$2:$B$1000, $B2)=$C2

 

Change Worksheet 1 to the real name of that sheet, and adjust the ranges if the data on the first sheet extend below row 1000.

Then fill down.

@hello12324343232131 

The @Hans Vogelaar solution using SUMIFS looks good.  I set out to use XLOKUP with 365 and got to

= MAP(firstNameWS2,surnameWS2,SalaryWS2,
      LAMBDA(fName,sName,salary,
         XLOOKUP(1, (firstNameWS1=fName)*(surnameWS1=sName),SalaryWS1) = salary
      )
   )

It returns the entire column with one formula but so would

= SUMIFS(
    SalaryWS1, 
    firstNameWS1, firstNameWS2, 
    surnameWS1,   surnameWS2) 
  = SalaryWS2

once I converted the salaries to numbers.