Embedded Files
Mail Merge add-on does not support merging data from multiple tabs and files. However, this article provides a work-around that enables the contents of multiple tabs and files to be quickly combined into a single tab for a mail merge.
Step 1: Create new tab
Step 1: Create new tab
Create a new tab within your Google Sheets file, which contains the combined data from a number of 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 holds the combined data from the English, Math, and Science tabs.
Step 2: Add column headings
Step 2: Add column headings
In your new combined tab containing the data, 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 is 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 are later populated with the data imported from the three original tabs.
Step 3: Populate cells in column
Step 3: Populate cells in column
Populate the cells in the column that is common to all tabs.
In the example below, the cells in the Student Name column are populated by copying and pasting the student names from one of the three original tabs.
Step 4: Insert VLOOKUP formula
Step 4: Insert VLOOKUP formula
In the first cell to be populated, insert the input =VLOOKUP formula.
A prompt appears detailing the parameters that 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 does not 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 (e.g., column B containing the Result).
is_sorted = false (the first column of the range should not be sorted in ascending order).
Step 5: Drag populated cell downward
Step 5: Drag populated cell downward
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 and 5
Step 6: Repeat steps 4 and 5
Repeat Steps 4 and 5 for the remaining column(s) and 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 same principles can be applied to include as many tabs as required.
The tab containing the combined data can now be used for 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
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 holds 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
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 holds 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 the VLOOKUP formula, refer to this article.
Report abuse