Outer join 3 sheets using formula

Viewed 31

I have 3 tables in 3 sheets. The email column is where the data is identified. Which means the emails will be unique in the expected results sheet after joining

To make a copy of the sheet click here To view the sheet click here

First Sheet

First Name
Last Name
Email
Phone
Childs First Name
Child Last Name
DOB
Photo Permission
Allergies
Additional Info
Additional Contact
Teacher Name(s)
Sibling First Name
Sibling Last Name
DOB2
Allergies 2
Additional Info 2
Photo Permission 2
How did hear?

Second

Second-First Name
Second-Last Name
Second-Phone
Email
Second-Message

Third

Third-First Name
Third-Last Name
Email

I have tried with

=FILTER({First!A2:B,
 VLOOKUP(First!C2:C, {Second!A2:A, Second!B2:D}, {2,3,4}, false)},
 First!C2:C<>"")

But I need to ensure all 3 sheets are joined and the expected result look like Expected image.png

First image.png Second image.png

Third image.png

1 Answers

There are three formulas in cyan on the tab called MK.Help in this editable copy of your sheet:

This is the one for the second set of names:

=ARRAYFORMULA({Second!A1:E1;ARRAY_CONSTRAIN(IFERROR(VLOOKUP(C2:C,{Second!D:D,Second!A:E},{2,3,4,5,6},0)),COUNTA(C2:C),5);FILTER(Second!A2:E,Second!D2:D<>"",ISNA(MATCH(Second!D2:D,C2:C,0)))})

That do what you're hoping?

Related