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
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
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
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
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:
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
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
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
- First, select the range that you want to import into another sheet. Keep note of the cell range and sheet name for later.
- 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.
- 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.
- Select the cell that will hold our
VLOOKUPfunction. In this example, we’ll start with cell C7. Our goal is to use the
VLOOKUPfunction to get the username of the user who made the order. This is possible since a user id field has been provided.
- Next, we must write down our
VLOOKUPfunction. For our second argument, we’ll now use the
IMPORTRANGEfunction to retrieve data from another spreadsheet.
- Hit the Enter key to evaluate the
- Use the Fill Handle tool to fill out the rest of the column.
- By changing the third argument in our
VLOOKUP, we can change which field to retrieve from our lookup table.
Frequently Asked Questions (FAQ)
- How often does IMPORTRANGE update?
IMPORTRANGEwill 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
- 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
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.
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.