VLOOKUP with IMPORTRANGE in Google Sheets

This guide will explain how you can use a lookup table found on another worksheet using VLOOKUP and IMPORTRANGE in Google Sheets.

Using IMPORTRANGE, we can pull data from another sheet we have access to.

Let’s take a look at a quick example of a use case for retrieving data from another sheet using IMPORTRANGE and VLOOKUP.

Suppose you have two spreadsheets that contain various tables needed for your business. The first sheet contains user information such as their address, full name, and unique user ID. The second sheet is a daily order tracker that includes data related to individual orders.

Each order has an associated user id. If you want to display user information in your order tracker, you must perform a VLOOKUP. Since our user table exists in another sheet, we will have to use the IMPORTRANGE function as well.

Now that we know when to use the VLOOKUP and IMPORTRANGE functions together, let’s look into how they can be used on an actual sample spreadsheet.

A Real Example of Using VLOOKUP with IMPORTRANGE in Google Sheets

Let’s take a look at an actual spreadsheet that uses VLOOKUP and IMPORTRANGE together in the same formula.

First, let’s take a look at our sample data. We have a dataset of users in an e-commerce platform. Each user has a unique id, name data, and their country of origin. Since each entry can be obtained through a unique id, this table can be used as a lookup table.

Suppose your workflow requires you to import the data into another sheet. This is common in scenarios where the original sheet is locked from editing, and the user does not want to copy and paste data manually.

If we require the user lookup table to fill in details, we can use the VLOOKUP and IMPORTRANGE functions together. In the example below, the username and last_name fields in our table are dynamically retrieved from an outside spreadsheet.

To get the values in the username column, we just need to use the following formula:

=VLOOKUP(B7,IMPORTRANGE($B$2,$B$3),4,false)

The first argument of the VLOOKUP formula references cell B7. This cell contains the user ID we can use as a lookup key. 

Typically, the second argument of the VLOOKUP formula contains a range. However, we’ll need to use the IMPORTANGE argument since our range is located outside our current spreadsheet.

You can make your own copy of the spreadsheet above using the link attached below. 

If you’re ready to use both VLOOKUP and IMPORTRANGE together in Google Sheets, follow our guide in the next section!

How to VLOOKUP with IMPORTRANGE in Google Sheets

This section will guide you through each step needed to start using the VLOOKUP and IMPORTRANGE together to look up values outside your sheet. You’ll learn how to reference an outside range using a single formula.

Follow these steps to start using the VLOOKUP function with the IMPORTRANGE function:

1. First, select the range that you want to import into another sheet. Keep note of the cell range and sheet name for later.
   
2. Next, we must retrieve the link to the actual spreadsheet. Click on the Share button on the top-right of your screen. In the pop-up dialog box, click on the Copy link option. The spreadsheet link should now be saved in your clipboard.
   
3. In the destination sheet, you may write down the URL of the spreadsheet and the range to import. The range must start with the sheet name separated by a ‘!’ character.
   
4. Select the cell that will hold our VLOOKUP function. In this example, we’ll start with cell C7. Our goal is to use the VLOOKUP function to get the username of the user who made the order. This is possible since a user id field has been provided.
5. Next, we must write down our VLOOKUP function. For our second argument, we’ll now use the IMPORTRANGE function to retrieve data from another spreadsheet.
   
6. Hit the Enter key to evaluate the VLOOKUP function.
   
7. Use the Fill Handle tool to fill out the rest of the column.
   
8. By changing the third argument in our VLOOKUP, we can change which field to retrieve from our lookup table.
   

Frequently Asked Questions (FAQ)

1. How often does IMPORTRANGE update?
   IMPORTRANGE will reload when a document is first opened or opened within the past 5 minutes. Because of the slight delays, it is best to avoid chains of IMPORTRANGE calls.
2. Do I need edit access to use IMPORTRANGE?
   The user only needs at least Viewer access to import data from a spreadsheet. When a spreadsheet pulls data from a new source sheet, Google Sheets will ask the user to grant the current sheet permission to access the source sheet.

This step-by-step tutorial should be all you need to use VLOOKUP with IMPORTRANGE in Google Sheets. This guide shows how using both these functions together allows you to import data across spreadsheets and lookup values through a single formula.

The IMPORTRANGE function is just one of many helpful functions you can use to consolidate data in Google Sheets. With so many other Google Sheets functions available, you can surely find one that suits your use case. 

Are you interested in learning more about what Google Sheets can do? Subscribe to our newsletter to find out about the latest Google Sheets guides and tutorials from us. 

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

Subscribe