Merge two Google sheets: update cells, add related columns & non-matching rows

Natalia Sharashova by , 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:
My lookup table with all the data.
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.

Merge Google sheets & add related columns using VLOOKUP

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:

=VLOOKUP(search_key, range, index, [is_sorted])
  • search_key is what you're looking for.
  • range is where you're looking for.
  • index is the number of the column to return the value from.
  • [is_sorted] is completely optional and indicates whether the key column is sorted.

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),""))
Use VLOOKUP to merge two Google sheets and update cells.
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),""))
Increase index in Google Sheets VLOOKUP by one.
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:

  • If you use a semicolon, numbers will take up different rows within a column:
    Create a vertical array in Google Sheets.
  • If you use a comma, those numbers will appear in separate columns in a row:
    Create a horizontal array in Google Sheets.

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),""))
Create an array of columns in Google Sheets VLOOKUP.
This way, one Google Sheets VLOOKUP formula matches names, updates stock info and adds related vendors into an empty adjacent column.

Match & merge sheets and add columns with INDEX MATCH

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)),"")
Merge Google sheets using INDEX MATCH.
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)),"")
Match 2 sheets, update Stock info, add vendors.
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)),"")
Add more columns using INDEX MATCH.

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:
When INDEX MATCH can't add columns.
Once you clear those cells or add new columns to the left of them, the formula results will appear.

Merge Google sheets, update cells & add related columns — all using QUERY

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&"'"),"")
How Google Sheets QUERY updates cells with the info from another table.
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:

  1. list all must-have columns for the select command:

    …select C,D,E…

  2. expand the range to look accordingly:

    …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&"'"),"")
Use Google Sheets QUERY to update cells and add related columns.
It updates the stock column and pulls 2 extra columns from the lookup table to this main table.

How to add non-matching rows using FILTER + VLOOKUP

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:

=FILTER(range, condition1, [condition2, ...])
  • range is the data you want to filter.
  • condition1 is a column or a row with a filtering criterion.
  • criteria2, criteria3, etc. are completely optional. Use them when you need to use several criteria.

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:
Add non-matching rows from one table to another.
Let me explain how it works:

  1. FILTER goes to the lookup sheet (a table with all the data — Sheet1!$A$2:$E$10) and uses VLOOKUP to get the correct rows.
  2. VLOOKUP takes the names of the items from column B on that lookup sheet and matches them with the names from my current table. If there's no match, VLOOKUP says there's an error.
  3. ISERROR marks each such error with 1, telling FILTER to take this row into another sheet.

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.

Formula-free way to match & merge data — Merge Sheets add-on

Merge Sheets add-on encompasses all 3 possibilities when merging Google sheets:

  • it updates related cells based on the matches
  • adds new columns for those matches
  • inserts rows with non-matching records

To avoid any confusion, the process is divided into 5 simple steps:

  • The first two are where you select your tables even if they are in different spreadsheets.
  • On the 3d, you are to choose key column(s) that should be checked for matches.
  • The 4th step lets you set the columns to update with new records or add from one sheet to another:
    Select action in Merge Sheets: update values or add a column to the end.
  • Finally, the 5th step has that checkbox that will make all non-matching rows appear at the end of your current table:
    Add non-matching rows to the end of the main table.

It took a few seconds till I could see the result:
How Merge Sheets updates records for matching data adds non-matching rows.
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 :)


Spreadsheet with formula examples

Merge Google sheets, add related columns & non-matching rows - formula examples (make a copy of this spreadsheet)

Table of contents