Forum Discussion

hello12324343232131's avatar
hello12324343232131
Copper Contributor
Sep 18, 2022

Match First + Last Name, then compare Salary

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!!

excel
Formulas and Functions
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Sep 18, 2022

    hello12324343232131 

    The HansVogelaar 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.

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Sep 18, 2022

    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.

Resources