Sep 18 2022 02:26 PM - edited Sep 18 2022 03:33 PM
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
John | Doe | $1000 |
Worksheet 2
Emily Doe $2500 False
John | Doe | $1000 | True |
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!!
Sep 18 2022 02:46 PM
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.
Sep 18 2022 03:29 PM
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.