by Natalia Sharashova, updated on
Today's blog post features all ways to merge 2 Google Sheets. You will use VLOOKUP, INDEX/MATCH, QUERY and Merge Sheets add-on to update cells in one sheet from records from another one based on matches in common columns.
The first thing you may turn to when you need to match and merge two Google sheets is the VLOOKUP function.
This function searches a column you specify for a certain key value and pulls one of the related records from the same row into another table or sheet.
Though Google Sheets VLOOKUP is usually considered as one of the difficult functions, it's actually quite straightforward and even easy once you get to know it.
Let's take a quick look at its components:
Note. VLOOKUP in Google Sheets always scans the first column of the range for the search_key.
E.g., if your range to search is A2:E20 and it's column E you need to get the data from, enter 5. But if your range is D2:E20, you'll need to enter 2 to get records from column E.
Tip. We have a detailed guide devoted to VLOOKUP in Google Sheets. Please check it out to learn more about the function, its peculiarities & limits, and get more formula examples.
With these arguments in mind, let's use VLOOKUP to merge two Google sheets.
Suppose I have a small table with berries and their IDs in Sheet2. Stock availability is unknown though:
Let's call this table the main one since my goal is to fill it in.
There's also another table in Sheet1 with all data in place, including stock availability:
I'll call it the lookup table since I will look into it to get the data.
I will use the Google Sheets VLOOKUP function to merge these 2 sheets. The function will match berries in both tables, and pull the corresponding "stock" info from the lookup into the main table.
Here's how this formula merges two Google sheets exactly:
Note. Remember, VLOOKUP scans the 1st column of the specified range — Sheet1!$B$2:$C$10.
Note. I use absolute references for the range because I copy the formula down the column and hence I need this range to stay the same in every row so the result doesn't break.
But what about those #N/A errors?
You see them in those rows where berries don't have matches in another sheet and there's nothing to return. Luckily, there's a way to keep such cells empty instead.
Just wrap your Google Sheets VLOOKUP in IFERROR:
Tip. Trap and fix other errors your Google Sheets VLOOKUP may return using solutions from this guide.
One more thing I'd like to mention is how to match and merge Google Sheets data for the entire column at once.
Nothing fancy here, just one more function — ArrayFormula.
Simply replace your one-cell key record in Google Sheets VLOOKUP with the entire column and put this whole formula inside ArrayFormula:
This way, you won't need to copy the formula down the column. ArrayFormula will return the correct result to each cell right away.
Although VLOOKUP in Google Sheets is perfect for such simple tasks, it has some limits. Here's one of the drawbacks: it can't look at its left. Whatever range you indicate, it always scans its first column.
Thus, if you need to merge 2 Google sheets and pull IDs (1st-column data) based on berries (2nd column), VLOOKUP won't help. You just won't be able to build a correct formula.
In cases like this, INDEX MATCH for Google Sheets enters the game.
INDEX MATCH, or rather INDEX & MATCH, are actually two different Google Sheets functions. But when they are used together, it's like a next-level VLOOKUP.
Yes, they also merge Google sheets: update cells in one table with records from another table based on common key records.
But they do that much better since they ignore all those limitations VLOOKUP has.
I won't cover all the basics today because I did that in this blog post. But I will give you a few INDEX MATCH formula examples so you could see how they work directly in Google spreadsheets. I will use the same sample tables from above.
First, let's merge those Google sheets and update the stock availability for all matching berries:
How do INDEX & MATCH work when used together like that?
Now let's try and test INDEX MATCH against what Google Sheets VLOOKUP cannot do — merge sheets and update the leftmost column with the required IDs:
Let's go further and get rid of those errors in cells with no matches. IFERROR will help again. Just put your Google Sheets INDEX MATCH as its first argument.
Now, how do you merge those Google sheets using INDEX MATCH and update all cells in the entire column at once?
Well… You don't. There's a little problem: ArrayFormula doesn't work with these two.
You will need to copy the INDEX MATCH formula down the column or use the Google Sheets QUERY function as an alternative.
Google Sheets QUERY is the most powerful function in spreadsheets. With this thing in mind, it's no surprise it offers a way to kind of merge tables — match & merge values from different sheets.
Tip. If you've never used Google Sheets QUERY before, this tutorial will get you through its peculiar language.
What should the QUERY formula look like to update the Stock column with the actual data?
=QUERY(Sheet1!$A$2:$C$10,"select C where B='"&Sheet4!$B2:$B$10&"'")
Let me just lose those errors for cells without matches:
=IFERROR(QUERY(Sheet1!$A$2:$C$10,"select C where B='"&Sheet4!$B2:$B$10&"'"),"")
Well, that's better :)
There's one more function I'd like to mention. It's important because it lets you merge sheets that reside in different Google spreadsheets (files).
The function is called IMPORTRANGE:
Note. I highly recommend going through Google docs on this function so you don't miss out on any important nuance of its work.
Imagine that your lookup sheet (with the reference data) is in Spreadsheet 2 (aka lookup spreadsheet). Your main sheet is in Spreadsheet 1 (main spreadsheet).
Note. For IMPORTRANGE to work, you must connect both files. And while Google Sheet suggest a button for that right after you type your formula in a cell and hit Enter, for the formulas below you may need to do that beforehand. This step-by-step guide will help you.
Below are the examples to merge Google sheets from different files using IMPORTRANGE with each function you've learnt earlier today.
Use IMPORTRAGE as a range in VLOOKUP to merge 2 separate Google spreadsheets:
As for INDEX MATCH & IMPORTRANGE, the formula becomes bulkier since you need to reference another spreadsheet twice: as a range for INDEX and as a range for MATCH:
This tandem of formulas is my personal favorite. They can deal with almost anything in spreadsheets when used together. Merging Google sheets from separate spreadsheets is not an exception.
=IFERROR(QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Sq…j7o/edit","Sheet1!$A$2:$C$10"),"select Col3 where Col2='"&QUERY!$B2:$B$10&"'"),"")
That's all for functions & formulas.
You are free to pick any function & build your own formula by the examples above…
...try a special tool that merges Google sheets for you! ;)
If you don't have time to build or even learn formulas, or if you're simply looking for the easiest way to join data based on common records, Merge Sheets will be perfect.
All you will need to do is tick off the checkboxes in 5 user-friendly steps:
There is even a possibility to save all the selected options into a scenario and reuse it whenever you need:
Watch this 3-minute demo video to see how it works:
I encourage you to install your Merge Sheets from the Google Sheets store and follow these instructions to try and update your own table with the info from another sheet.
Merge Google sheets & update data - formula examples (make a copy of the file)
Table of contents