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

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 :)

Let's do a quick formula syntax recap:

• 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.

When I merged 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:

• If you use a semicolon, numbers will take up different rows within a column:
• If you use a comma, those numbers will appear in separate columns in a row:

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.

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.

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)),"")

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.

Let's recall the formula that updates cells first:

=IFERROR(QUERY(Sheet1!$A$2:$C$10,"select C where B='"&Sheet4!$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:

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&"'"),"")

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 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.

Google Sheets FILTER arguments are pretty clear:

• 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.

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 Google tables for matches and pulls non-matching rows from one table to the other:

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:
• Finally, the 5th step has that checkbox that will make all non-matching rows appear at the end of your current table:

It took a few seconds till I could see the result:

Spreadsheet with formula examples

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