Auto merge data from multiple sheets in a google spreadsheet

Ok, so I heard that you want to merge multiple sheets (let's say 2) in a google spreadsheet? That's easy (after hours of trying :"D)

Assuming you have 2 sheets as following:

Sheet1




Sheet2:



In the merged list or master list you need these information:

First Name, Last Name, Company, Job Title, Email Address

1. First thing you need to do is to find out a field to use as merge key. It's Email Address in this case. You also should be noticed that there should be no duplicate email addresses. So in order to get all the unique email addresses from 2 sheets, you need to use UNIQUE. But UNIQUE will also give you a blank row (ending row), so FILTER will do the job:

=FILTER(UNIQUE({Sheet1!H2:H;Sheet2!E2:E}),NOT(ISBLANK(unique({Sheet1!H2:H;Sheet2!E2:E}))))

Notes:

• Look at how I refer to another sheet (Sheet!H2:H...)
• You only enter the formula in cell E2 of the Master list, all the email addresses will be filled in other rows automatically.

2. Then you can do lookup(s) to get other field values:

For example, to get First Name of the person that have email address in column E of the Master list, you will need this formula in cell A2:

=IF(NOT(ISNA(QUERY({Sheet1!$A$2:$H}, "select Col1 where Col8='"&E2&"'"))),
        QUERY({Sheet1!$A$2:$H}, "select Col1 where Col8='"&E2&"'"),
        QUERY({Sheet2!$A$2:$I}, "select Col3 where Col5='"&E2&"'"))

Notes:

Colx is the replacement for the column (A,B,C...) in the querying sheet. For example, in this statement:

QUERY({Sheet1!$A$2:$H}, "select Col1 where Col8='"&E2&"'")

"Col1" is "First Name" column of Sheet1.
"Col8" is Email Address column of Sheet1.



And this is the result: