Match First + Last Name, then compare Salary

Occasional Visitor

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

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

2 Replies

Re: Match First and Last names across to worksheets, then compare third data.

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.

Re: Match First and Last names across to worksheets, then compare third data.

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.