CODEWITHSUNDEEP
excel
LuckyFalkor84
LuckyFalkor84

Reputation: 607

Excel - matching records between worksheets based on more than one column

I have two worksheets basic format of them both is:

I am trying to make a column on the second worksheet (which is significantly smaller then the other one) that tells me if a record worksheet 2 matches a record in worksheet 1, based on the first and last name.

This filter almost got me what I wanted:

=IF(ISERROR(MATCH(B2:C2,Table2[last name]:Table2[First Name],0)),"user gone","user exists")

Except it would return true if user's first name matched another's first name in worksheet 1 and user's last name matched someone else's last name. Example:

I need the filter to return true only if jane doe exists in both lists. How can i tweak my filter to do this? i keep finding ways to match single columns in records not multi columns between records.

Upvotes: 0

Views: 298

Answers (1)

PerpetualStudent
PerpetualStudent

Reputation: 832

You can do this pretty easily with an array formula.

Be sure to enter as an array formula using ctrl+shift+enter:

=IF(SUM((B2=Table2[last name])*(C2=Table2[First Name])*1),"user exists","user gone")

The sum searches for the name and returns a one or a zero, and the if statement assigns it a value.

Upvotes: 1

Related Questions