Merge from Multiple Sheets

Can I mail merge data from different tabs and files?

While the Mail Merge add-on doesn't support merging data from multiple tabs and files, this article provides a workaround which enables the contents of multiple tabs and files to be quickly combined into a single tab to mail merge.

Step 1

Create a new tab within your Google Sheets file, which will contain the combined data from a number of other tabs.

In the example below, a school secretary has a Google Sheets file containing three tabs of student exam results. Each tab contains the results for one school subject, but for many students. The secretary wants to mail merge to emails, with each merged email containing the exam results for one student across all subjects. As it is not possible to merge from multiple tabs, the secretary first creates a new tab called 'Combined' which will hold the combined data from the 'English', 'Math' and 'Science' tabs.

Step 2

In your new combined data tab, add column headings.

Your new combined data tab should have one column in common with the original tabs containing your data. The data in this column will be used as a search key to find and import the relevant data from your original tabs.

In the example below, the first column heading is 'Student Name'. This column is also included in each of the three original tabs. Additional column headings are added for 'English Result', 'Math Result' and 'Science Result'. These columns will later be populated with the data imported from the three original tabs.

Step 3

Populate the cells in the column which is common to all tabs.

In the example below, the cells in the 'Student Name' column are populated with student names. The names are simply copied and pasted from one of the three original tabs.

Step 4

In the first cell to be populated, input =VLOOKUP

A prompt will appear detailing the parameters which need to be specified: VLOOKUP(search_key, range, index, [is_sorted])

Where:

  • search_key = the cell containing the unique value to look up

  • range = the tab name and cell range to search (separated by an exclamation mark "!") - this should include the column containing the search_key

  • index = the column within the range from which the value should be retrieved - where the first column is 1, the second column is 2 etc.

  • is_sorted = determines whether the first column of the range should be sorted in ascending order - can be true or false

Add values for the parameters above for the data in your first tab.

NOTE: Adding dollar signs "$" before the column and row values fixes the range so that it doesn't change when copied to other cells - this can be done by pressing the F4 key.

In the example below, cell B2 needs to be populated with the 'English Result' for 'Samuel Reeves'. Therefore, the following parameters are specified: =VLOOKUP(A2,English!$A$2:$B$11,2, false)

Where:

  • search_key = A2 - Samuel Reeves

  • range = English!A2:C11 - 'English' tab, cells A2 to B11 (English!$A$2:$B$11 is used to fix the range - see note above)

  • index = 2 - second column in the range, i.e. column B holding 'Result'

  • is_sorted = false - the first column of the range shouldn't be sorted in ascending order

Step 5

Drag the populated cell downward from the bottom-right corner to apply the VLOOKUP formula to the remaining cells in the column.

In the example below, the English exam result is populated for each student listed.

Step 6

Repeat Steps 4 & 5 for the remaining column(s) / tab(s).

In the example below, the Math and Science exam results are populated for each student listed. While the example is limited to three tabs, the principles can be applied to include as many tabs as required.

The tab containing the combined data can now be easily used in a mail merge.

In the example, the school secretary has combined the exam results from the original three tabs ('English', 'Math' and 'Science') into one tab ('Combined'). In the combined tab, each row contains the exam results for one student across all subjects. This tab can now be easily used to mail merge to emails, with each merged email containing the exam results for one student across all subjects.

Advanced Feature: Using VLOOKUP with Another Google Sheets File

With the addition of the IMPORTRANGE formula, the VLOOKUP formula can be used to search another Google Sheets file for data to import: IMPORTRANGE("spreadsheet_key", "range_string")

Where:

  • spreadsheet_key = the URL of the file to import data from

  • range_string = the tab and range of cells to search - this should include the column containing the search_key

The IMPORTRANGE function replaces the range parameter in the VLOOKUP formula: VLOOKUP(search_key, IMPORTRANGE("spreadsheet_key", "range_string"), index, [is_sorted])

NOTE: To access the data in another file, you need to be the creator of the file, or be given the relevant permissions by the creator.

In the example below, this time a school secretary has three files containing student exam results. Each file contains the results for one school subject, but for many students. The secretary wants to mail merge to emails, with each merged email containing the exam results for one student across all subjects. As it is not possible to merge from multiple files, the secretary first creates a new file called 'Combined Exam Results' which will hold the combined data from the 'English Exam Results', 'Math Exam Results' and 'Science Exam Results' files. The VLOOKUP formula is used to import data from the other files.

Therefore, the following parameters are specified in the VLOOKUP formula: VLOOKUP(A2,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1MFex_66UCay4cbn0FSOpQ3BiiGfVIAEjKXBKzg2MgNQ/edit#gid=1572863759","Sheet1!$A$2:$B$11"),2,false)

Where:

  • spreadsheet_key = https://docs.google.com/spreadsheets/d/1MFex_66UCay4cbn0FSOpQ3BiiGfVIAEjKXBKzg2MgNQ/edit#gid=1572863759 - 'Combined Exam Results' file

  • range_string = Sheet1!$A$2:$B$11 - Sheet1, cells A2 to B11

While the example is limited to three files, the principles can be applied to include as many files as required.

Advanced Feature: Using VLOOKUP with Multiple Tabs

By modifying the VLOOKUP formula, it can be used to search multiple tabs for data to import: VLOOKUP(search_key, {range1; range2; range3}, index, [is_sorted])

Where range1, range2 and range3 are the tab name and cell range to search for the first, second and third tabs respectively.

In the example below, this time a school secretary has a Google Sheets file containing three tabs of English exam results. Each tab contains the English results for a class of students. The secretary wants to mail merge to emails, with each merged email containing the English result for a student from one of the three classes. As it is not possible to merge from multiple tabs, the secretary first creates a new tab called 'Combined' which will hold the combined data from the 'Class 1', 'Class 2' and 'Class 3' tabs. The VLOOKUP formula is used to search all three tabs for the relevant data.

Therefore, the following parameters are specified in the VLOOKUP formula: VLOOKUP(A2,{'Class 1'!$A$2:$B$11;'Class 2'!$A$2:$B$11;'Class 3'!$A$2:$B$11},2, false)

Where:

  • range1 = Class 1'!$A$2:$B$11 - Class 1 tab, cells A2 to B11

  • range2 = Class 2'!$A$2:$B$11 - Class 2 tab, cells A2 to B11

  • range3 = Class 3'!$A$2:$B$11 - Class 3 tab, cells A2 to B11

While the example is limited to three tabs, the principles can be applied to include as many tabs as required.

For more information on VLOOKUP, please refer to this article.