How to merge Google sheets: lookup, match and update records

Today's blog post features all ways to merge Google Sheets: as many as you need. You will learn to use VLOOKUP, INDEX/MATCH, QUERY and Merge Sheets add-on. All of them update cells in one sheet from records from other ones based on matches in common columns.

1. Merge Google sheets using VLOOKUP function

The first thing you may turn to when you need to match and merge Google sheets is the VLOOKUP function.

VLOOKUP syntax & usage

This function scans a column you specify for a certain key value. Once the match is there, it pulls the related records from the same row into another table or sheet.

Though Google Sheets VLOOKUP is usually considered one of the difficult functions, it's super straightforward once you get to know it.

Let's take a quick look at VLOOKUP components:

=VLOOKUP(search_key, range, index, [is_sorted])
  1. search_key is the key value you're looking for. It can be any text string, number, or a cell reference.
  2. 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.

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

  4. [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 on 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.

I have my main table with berries and their IDs in the Products sheet. Stock availability is unknown though: The table I'm going to update since the data is missing in column C.

There's also another table in Sheet1 with all data in place, including stock availability: My lookup table with data.

It'll be my lookup table since I will look into it to get the data.

I will use Google Sheets VLOOKUP to merge these sheets:

  1. It will first match berries in both tables.
  2. And then pull the corresponding "stock" info from the lookup into the main table.

=VLOOKUP(B2,Sheet1!$B$2:$C$10,2,FALSE) Match and merge data using VLOOKUP in Google Sheets.

Here's how this VLOOKUP merges Google sheets exactly:

  1. 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. This way, this range will stay the same in every row and the result won't break.

  2. FALSE at the end says that data in column B (in the lookup sheet) is not sorted so only exact matches will be considered.
  3. Once there's a match, Google Sheets VLOOKUP pulls the related record from the 2nd column of that range (column C).

Catch VLOOKUP errors 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),"") Hide errors using the IFERROR function.

Tip. If your Google Sheets VLOOKUP returns some other errors, you will trap and fix them using solutions from this guide.

Match & update entire column at once — ArrayFormula

One more trick I have to share is how to match and merge Google Sheets for the whole column at once. Nothing fancy here, just one more function — ArrayFormula:

  1. Simply replace your one-cell reference (B2) in Google Sheets VLOOKUP with the entire column (B2:B10).
  2. And put this whole formula inside the ArrayFormula function.

    3. =ArrayFormula(IFERROR(VLOOKUP(B2:B10,Sheet1!$B$2:$C$10,2,FALSE),"")) VLOOKUP in the ArrayFormula to get results for all cells in the column.

    This way, you won't need to copy your VLOOKUP down the column. ArrayFormula will pull the correct result for each cell right away.

    Tip. Find more details about ArrayFormula in this blog post.

    Although VLOOKUP in Google Sheets is perfect for such simple tasks, it has a major drawback: it can't look at its left. Whatever range you indicate, it always scans its first column.

    Thus, if you need to merge two or more Google sheets, and for example pull IDs (1st-column data) based on berries (2nd column), VLOOKUP won't help.

    In cases like this, INDEX MATCH for Google Sheets enters the game.

2. 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 matching key records. But they ignore all those limitations VLOOKUP has doing a much better job!

I won't cover all the basics below because I did that in this blog post. But I will show you how to use INDEX MATCH to specifically merge Google sheets. I will use the same sample tables from above.

1. Update data to the right of the matching column

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)) Update data using INDEX MATCH in Google Sheets.

How do INDEX & MATCH merge sheets like that?

  1. MATCH looks at B2 and searches for the exact same record in column B on Sheet1.
  2. Once found, it returns the number of the row that contains that value — 10 in my case.
  3. INDEX goes to that 10th row on Sheet1 as well, only it pulls the value from another column — C.

One common thing INDEX MATCH have with VLOOKUP is errors in cells with no matches. The solution is also the same: IFERROR will help again. Just put your Google Sheets INDEX MATCH as its first argument.

=IFERROR(INDEX(Sheet1!$C$1:$C$10,MATCH(B2,Sheet1!$B$1:$B$10,0)),"") Incorporate IFERROR to have empty cells instead of errors.

2. Update data to the left of the matching column

Now let's try and test INDEX MATCH against what VLOOKUP cannot do — merge Google sheets and update the leftmost column with the required IDs:

=INDEX(Sheet1!$A$2:$A$10,MATCH(B2,Sheet1!$B$2:$B$10,0)) INDEX MATCH updates the leftmost column.

It works just like the first INDEX MATCH because it doesn't matter what column to look at. Easy-peasy :)

To fix those #N/A errors, once again, wrap INDEX MATCH in IFERROR:

=IFERROR(INDEX(Sheet1!$A$2:$A$10,MATCH(B2,Sheet1!$B$2:$B$10,0)),"") IFERROR for the leftmost column.

Now, how do you use INDEX MATCH to merge those Google sheets 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.

3. Use QUERY to merge Google sheets & update cells

Google Sheets QUERY is the most powerful function in spreadsheets. So it's no surprise it offers a way to merge two or more Google sheets (match & update values).

Tip. If you've never used Google Sheets QUERY before, this tutorial will get you through its peculiar language.

Here's how the basic QUERY formula looks:

=QUERY(data, query, [headers])

How do you use it to merge two sheets and update the Stock column with the actual data?

=QUERY(Sheet1!$A$2:$C$10,"select C where B='"&Products!$B2:$B$10&"'") QUERY updates one column with the info from another sheet.

  1. Google Sheets QUERY looks at my lookup sheet — Sheet1 — since there are records I want to pull to my main table.
  2. And returns all those cells from column C only where column B matches berries in my main table.

Additionally, let me just lose those errors for cells without matches using IFERROR:

=IFERROR(QUERY(Sheet1!$A$2:$C$10,"select C where B='"&Products!$B2:$B$10&"'"),"") Wrap QUERY in IFERROR to dispose of errors.

Well, that's better :)

4. Merge Google sheets from different files — IMPORTRANGE function

There's one more function I'd like to mention. It's important because it lets you merge multiple sheets even from different Google spreadsheets (files).

The function is called IMPORTRANGE:

=IMPORTRANGE("spreadsheet_url","range_string")
  1. It starts with a link to a spreadsheet where you pull the data from (lookup spreadsheet).
  2. Followed by a sheet & a 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 reference data) is in Spreadsheet 2 (lookup spreadsheet). Your main sheet is in Spreadsheet 1 (main spreadsheet).

Note. For IMPORTRANGE to work, you must connect both files. Google Sheets always suggests a button for that right after you enter your formula. You can learn more about it in this step-by-step guide.

Let's use IMPORTRANGE with each function you learned earlier to merge Google sheets from different files.

Example 1. IMPORTRANGE + VLOOKUP

Use IMPORTRAGE as a range VLOOKUP to merge two Google sheets from different files:

=ArrayFormula(IFERROR(VLOOKUP(B2:B10,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Sq…j7o/edit","Sheet1!$B$2:$C$10"),2,FALSE),"")) Match and pull data from another Google file using IMPORTRANGE inside VLOOKUP.

Example 2. IMPORTRANGE + INDEX MATCH

As for INDEX MATCH & IMPORTRANGE, you will 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)),"") IMPORTRANGE + INDEX MATCH in one formula.

Example 3. IMPORTRANGE + QUERY

This tandem of formulas is my personal favourite. Togethe, they can deal with almost anything in spreadsheets when used together. Merging Google sheets from separate files 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&"'"),"") ALT

Voila :) 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! ;)

Easiest way to match & merge Google sheets — Merge Sheets add-on

If you don't have time to build or even learn formulas, or if you're simply looking for the easiest way to match and merge Google Sheets, Merge Sheets add-on is what you're looking for.

All you will need to do is tick off the checkboxes in 5 user-friendly steps:

  1. select your main sheet
  2. select your lookup sheet
  3. tick off key columns (those that should match)
  4. choose columns to update and/or add: Select columns to add and update.
  5. adjust additional options, e.g., where to place the results, or color updated records, or mark them in a status column, etc.
  6. click Merge

Once the add-on does the job, it will even offers to save this run into a scenario. You will be able to reuse it from the Google Sheets menu in a click whenever you need: Save the options into the scenario to use later.

Video: Easiest way to merge Google sheets

Watch this 3-minute demo video to see how Merge Sheets add-on works:


I encourage you to install Merge Sheets from the Google Sheets store and follow these instructions to match and merge your tables. And let me know how it goes in the comments section!

Spreadsheet with formula examples

Merge Google sheets & update data - formula examples (make a copy of the file)

Table of contents

6 comments

  1. I am trying to build a script where a document is created for every row in a sheet where there is an unknown number of rows in the sheet. The sheet was created by Forms and continues to grow as replies are recorded. My script works great to create the documents but keeps on running creating blank documents until I Cancel the script. How do I end the execution once the end of the data is reached?

    function createNewGoogleDocs() {
    //This value is the id of the document template
    const googleDocTemplate = DriveApp.getFileById('XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');

    //This value is the id of the folder where completed documents are stored
    const destinationFolder = DriveApp.getFolderById('XXXXXXXXXXXXXXXXXXXXXXXXXXXX')
    //Here we store the sheet as a variable
    const sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName('Comparrison')

    //Now we get all of the values as a 2D array
    const rows = sheet.getDataRange().getValues();

    //Start processing each spreadsheet row
    rows.forEach(function(row, index){
    //Here we check if this row is the headers, if so we skip it
    if (index === 0) return;
    if (index === 1) return;
    //Here we check if a document has already been generated by looking at 'Document Link', if so we skip it
    if (row[53]) return;
    //Using the row data in a template literal, we make a copy of our template document in our destinationFolder
    const copy = googleDocTemplate.makeCopy(`${row[0]} Health Status Summary` , destinationFolder)
    //Once we have the copy, we then open it using the DocumentApp
    const doc = DocumentApp.openById(copy.getId())
    //All of the content lives in the body, so we get that for editing
    const body = doc.getBody();

    //In these lines, we replace our replacement tokens with values from our spreadsheet row
    body.replaceText('{{Email Address}}', row[0]);
    body.replaceText('{{Digestive Track Before}}', row[1]);
    body.replaceText('{{Digestive Track After}}', row[2]);
    body.replaceText('{{Digestive Track Delta}}', row[3]);

    etc etc

    //We make our changes permanent by saving and closing the document
    doc.saveAndClose();
    //Store the url of our new document in a variable
    const url = doc.getUrl();
    //Write that value back to the 'Document Link' column in the spreadsheet.
    sheet.getRange(index + 1, 54).setValue(url)

    })
    }

  2. Hello
    I tired =IMPORTRANGE("spreadsheet_url","range_string") but it can only import the first sheet of the workbook.

    Is there a way to import the second page onwards?

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

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)