by Natalia Sharashova, updated on
Did you know that when you merge 2 Google sheets you can not only update records in one column but also pull whole related columns and even non-matching rows? Today I will show you how it's done with VLOOKUP, INDEX/MATCH, QUERY functions and the Merge Sheets add-on.
The last time I talked about merging 2 Google sheets, I shared ways to match & update data. This time, we'll still update cells but will also pull other related columns and non-matching rows.
Here's my lookup table. I'm going to take all the necessary data from it today:
It's got bigger this time: it has two extra columns with vendor names and their ratings. I will update the Stock column with this info in another table and will also pull vendors. Well, maybe ratings as well :)
As usual, I will use a few functions and a special add-on for the job.
Remember Google Sheets VLOOKUP? I used it in my previous article to match data and update some cells.
If this function still scares you away, it's high time to face it and learn it once and for all because I'm going to use it today as well :)
Tip. If you're looking for a quick solution to save your time, go meet Merge Sheets right away.
Let's do a quick formula syntax recap:
Tip. There's a whole tutorial devoted to Google Sheets VLOOKUP on our blog, feel free to have a look.
When I merged two Google sheets and simply updated the data in the Stock column, I used this VLOOKUP formula:
=ArrayFormula(IFERROR(VLOOKUP($B$2:$B$10,Sheet1!$B$2:$D$10,2,FALSE),""))
IFERROR made sure there were no errors in cells without matches and ARRAYFORMULA processed the entire column at once.
So what changes do I need to make to pull vendors as a new column from the lookup table as well?
Well, since it's the index that tells Google Sheets VLOOKUP what column it should take the data from, it's safe to say it's the one that needs tweaking.
The simplest way would be to just copy the formula into the neighboring column and increase its index by one (replace 2 with 3):
=ArrayFormula(IFERROR(VLOOKUP($B$2:$B$10,Sheet1!$B$2:$D$10,3,FALSE),""))
However, you'll need to insert the same formula with a different index as many times as many additional columns you'd like to get.
Fortunately, there's a better alternative. It involves creating arrays. Arrays let you combine all columns you'd like to pull in one index.
When you create an array in Google Sheets, you list values or cell/range references in brackets, e.g. ={1, 2, 3} or ={1; 2; 3}
The arrangement of these records in a sheet depends on the delimiter:
The latter is exactly what you need to do in the Google Sheets VLOOKUP index argument.
Since I merge Google sheets, update the 2nd column and pull the 3rd one, I need to create an array with these columns: {2, 3}:
=ArrayFormula(IFERROR(VLOOKUP($B$2:$B$10,Sheet1!$B$2:$D$10,{2,3},FALSE),""))
This way, one Google Sheets VLOOKUP formula matches names, updates stock info and adds related vendors into an empty adjacent column.
Next up is INDEX MATCH. These two functions together compete with VLOOKUP as they bypass its limitations when merging Google sheets.
Tip. Get to know INDEX MATCH for Google Sheets in this tutorial.
Let me start by reminding you of the formula that simply merges one column based on the matches:
=IFERROR(INDEX(Sheet1!$C$1:$C$10,MATCH(B2,Sheet1!$B$1:$B$10,0)),"")
In this formula, Sheet1!$C$1:$C$10 is a column with the values you need whenever Sheet1!$B$1:$B$10 meets the same value as in B2 in the current table.
With these points in mind, it is Sheet1!$C$1:$C$10 that you need to change in order to not just merge tables and update cells but also add columns.
Unlike Google Sheets VLOOKUP, nothing fancy here. You just enter the range with all those required columns: the one to update and others to add. In my case, it'll be Sheet1!$C$1:$D$10:
=IFERROR(INDEX(Sheet1!$C$1:$D$10,MATCH(B2,Sheet1!$B$1:$B$10,0)),"")
Or I can expand the range to E10 to add 2 columns, not just one:
=IFERROR(INDEX(Sheet1!$C$1:$E$10,MATCH(B2,Sheet1!$B$1:$B$10,0)),"")
Note. Those extra records always fall into the neighboring columns. If those columns will have some other values, the formula won't overwrite them. It will give you a #REF error with a corresponding hint:
Once you clear those cells or add new columns to the left of them, the formula results will appear.
QUERY is one of the most powerful functions in Google spreadsheets. So it's no surprise I'm going to use it today to merge some Google sheets, update cells and add extra columns at the same time.
This function differs from others because one of its arguments uses a command language.
Tip. If you're wondering how to use the Google Sheets QUERY function, visit this blog post.
Let's recall the formula that updates cells first:
=IFERROR(QUERY(Sheet1!$A$2:$C$10,"select C where B='"&QUERY!$B2:$B$10&"'"),"")
Here QUERY looks at the table with the required data in Sheet1, matches cells in column B with my current new table, and merges these sheets: pulls data from column C for every match. IFERROR keeps the result error-free.
To add extra columns for those matches, you need to make 2 small changes to this formula:
…select C,D,E…
…QUERY(Sheet1!$A$2:$E$10,…
Here's a full formula:
=IFERROR(QUERY(Sheet1!$A$2:$E$10,"select C,D,E where B='"&Sheet4!$B2:$B$10&"'"),"")
It updates the stock column and pulls 2 extra columns from the lookup table to this main table.
Imagine this: you merge 2 Google sheets, update old info with the new one, and get new columns with extra related values.
What else could you do to have a full picture of the records at hand?
Perhaps adding non-matching rows to the end of your table? This way, you'll have all values in one place: not only matches with the updated related info but also non-matches as well to make them count.
I was pleasantly surprised that Google Sheets VLOOKUP knows how to do that. When used together with the FILTER function, it merges Google sheets and adds non-matching rows as well.
Tip. In the end, I will also show how one add-on does the same with a single checkbox.
Google Sheets FILTER arguments are pretty clear:
Tip. You will learn more about the Google Sheets FILTER function in this blog post.
So how do these two functions get along together and merge Google sheets? Well, FILTER returns the data based on the filtering criteria created by VLOOKUP.
Look at this formula:
=FILTER(Sheet1!$A$2:$E$10,ISERROR(VLOOKUP(Sheet1!$B$2:$B$10,$B$2:$C$10,2,FALSE)=1))
It scans 2 Google tables for matches and pulls non-matching rows from one table to the other:
Let me explain how it works:
As a result, the formula pulls 3 additional rows for those berries that don't occur in my main table.
It's not that complicated once you play around with this method a bit :)
But if you don't want to spend your time on this, there's a better and quicker way — without a single function and formula.
Merge Sheets add-on encompasses all 3 possibilities when merging Google sheets:
To avoid any confusion, the process is divided into 5 simple steps:
It took a few seconds till I could see the result:
Install Merge Sheets from the Google Sheets store and you'll see that it processes bigger tables just as fast. Thanks to Merge Sheets, you'll have more time for important matters.
I will also leave this 3-minute demo video to help you make up your mind :)
Merge Google sheets, add related columns & non-matching rows - formula examples (make a copy of this spreadsheet)
Table of contents