Google sheets function : Filter records by multiple condition

0
votes

I have 2 sheets : Sheet1 has large number of records with columns Firstname, Lastname and Company. Another Sheet2 has same columns but small set of records. I want only those records from sheet1 which have a matching firstname and lastname in sheet2 but company is different. How can I achieve this with help of a formula? (it has to be scale-able). E.g. Sheet1

Firstname   Lastname    Company
John         Doe         ABC
Jon          Smith       XXX
Dan          S.          XXX
John         Davies      XXX

Sheet2

Firstname   Lastname    Company
John        Davies      ABC
Jon         Smith       XXX

Expected output :

Firstname   Lastname    Company
John        Davies      XXX
1
google-sheets-formula

1 Answers

0
votes

Try below formula:

=SORT(IFERROR(ARRAYFORMULA(SPLIT(ARRAYFORMULA(VLOOKUP(ARRAYFORMULA(E2:E&" "&F2:F&" "&C2:C),ARRAYFORMULA(A2:A&" "&B2:B&" "&C2:C),1,0))," ")),""),1,false)