by 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.
Merge Google sheets using the VLOOKUP function
The first thing you may turn to when you need to match and merge two Google sheets is the VLOOKUP function.
Syntax & usage
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:
- search_key is the key value you're looking for. It can be any text string, number, or a cell reference.
- range is that group of cells (or a table) where you will look for the search_key and where you will pull the related records from.
Note. VLOOKUP in Google Sheets always scans the first column of the range for the search_key.
- index is the number of the column within that range where you want to pull the data from.
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.
- [is_sorted] is the only argument that you can omit. It's used to say if the column with key values is sorted (TRUE) or not (FALSE). If TRUE, the function will work with the closest match, if FALSE — with a complete one. When omitted, TRUE is used by default.
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.
=VLOOKUP(B2,Sheet1!$B$2:$C$10,2,FALSE)
Here's how this formula merges two Google sheets exactly:
- It looks for the value from B2 (main sheet) in column B on Sheet1 (lookup sheet).
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.
- FALSE at the end says that data in column B (in the lookup sheet) is not sorted so only exact matches will be considered.
- Once there's a match, Google Sheets VLOOKUP pulls the related record from the 2nd column of that range (column C).
Hide errors returned by VLOOKUP in Google Sheets — IFERROR
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:
=IFERROR(VLOOKUP(B2,Sheet1!$B$2:$C$10,2,FALSE),"")
Tip. Trap and fix other errors your Google Sheets VLOOKUP may return using solutions from this guide.
Match & update records for the entire column at once — ArrayFormula
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:
=ArrayFormula(IFERROR(VLOOKUP(B2:B10,Sheet1!$B$2:$C$10,2,FALSE),""))
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.
Match & merge Google sheets using INDEX MATCH duo
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.
INDEX MATCH in action in Google Sheets
First, let's merge those Google sheets and update the stock availability for all matching berries:
=INDEX(Sheet1!$C$1:$C$10,MATCH(B2,Sheet1!$B$1:$B$10,0))
How do INDEX & MATCH work when used together like that?
- MATCH looks at B2 and searches for the exact same record in column B on Sheet1. Once found, it returns the number of the row that contains that value — 10 in my case.
- INDEX goes to that 10th row on Sheet1 as well, only it takes the value from another column — C.
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:
=INDEX(Sheet1!$A$2:$A$10,MATCH(B2,Sheet1!$B$2:$B$10,0))
Easy-peasy :)
Handle errors returned by INDEX MATCH in Google Sheets
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.
Example 1.
=IFERROR(INDEX(Sheet1!$C$1:$C$10,MATCH(B2,Sheet1!$B$1:$B$10,0)),"")
Example 2.
=IFERROR(INDEX(Sheet1!$A$2:$A$10,MATCH(B2,Sheet1!$B$2:$B$10,0)),"")
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.
Merge Google sheets & update cells using QUERY
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&"'")
- Google Sheets QUERY looks at my lookup sheet (Sheet1 with the records I need to pull to my main table)
- and returns all those cells from column C where column B matches berries in my main table
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 :)
Merge tables from different Google spreadsheets — IMPORTRANGE function
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:
- the former goes the link to that spreadsheet where you pull the data from
- the latter goes the sheet & the range that you want to take from that spreadsheet
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.
Example 1. IMPORTRANGE + VLOOKUP
Use IMPORTRAGE as a range in VLOOKUP to merge 2 separate Google spreadsheets:
=ArrayFormula(IFERROR(VLOOKUP(B2:B10,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Sq…j7o/edit","Sheet1!$B$2:$C$10"),2,FALSE),""))
Example 2. IMPORTRANGE + INDEX MATCH
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:
=IFERROR(INDEX(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Sq…j7o/edit","Sheet1!$A$1:$A$10"),MATCH(B2,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Sq…j7o/edit","Sheet1!$B$2:$B$10"),0)),"")
Example 3. IMPORTRANGE + QUERY
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&"'"),"")
Whew!
That's all for functions & formulas.
You are free to pick any function & build your own formula by the examples above…
or…
...try a special tool that merges Google sheets for you! ;)
Formula-free way to match & merge data — Merge Sheets add-on for Google Sheets
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:
- select your main sheet
- select your lookup sheet
- mark key columns (those that contain records to match) with checkboxes
- choose columns to update:
- adjust additional options, e.g., mark updated records with color or in a status column, etc.
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.
Spreadsheet with formula examples
Merge Google sheets & update data - formula examples (make a copy of the file)
2 comments
Hi,
I am currenty using a query formula to pull through data from one sheet onto another sheet based on a set criteria but also where one piece of data in column A on the sheet the data is being pulled into and also has a to match one piece of data in the other sheet in column A ( this piece of data changes on each row in both sheets)
I am currently using the below formula but they last command where A matches '"&A:A&"' is not working.
=IFNA(QUERY('Data Dump'!$A$1:$Z,"select Q where A is not null and M matches '"&L$2&"' and A matches '"&A:A&"' ",0),"")
Is this possible within a query formula and if so what amends do I need to make to my existing formula.
Thanks you so much for your time and help.
Best wishes
Emily
Hi Emily,
For me to be able to help you better, please share an editable copy of your spreadsheet with your formula with us: support@apps4gs.com. Please include the example of the result you expect to get.
If you have confidential information there, you can replace it with some irrelevant data, just keep the format.
I'll look into it and try to help.
Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.