How to use VLOOKUP with IMPORTRANGE in Google Sheets

Combining the power of VLOOKUP and IMPORTRANGE is a great way to transfer specific data between two spreadsheets. Expand the horizons of both functions and apply vlookup across Google Sheets.

Continue reading to use VLOOKUP across Google Sheets and discover how Sheetgo, an AI-powered data automation platform, offers an alternative that needs no formulas.

If the data you want to look up is in another tab of the same file, you don’t need IMPORTRANGE. Our guide to VLOOKUP from another sheet or workbook covers that case.

An overview of IMPORTRANGE

Let’s review how IMPORTRANGE works. It’s straightforward and only needs two arguments: the URL of the source spreadsheet and the range to import from it.

A few pointers to keep in mind:

• You must wrap both arguments in quotation marks.
• You need view access to the source spreadsheet.
• This function will return a REF error the first time it runs. To solve it, click the cell that contains the error and then allow access to the source spreadsheet.

Check out our ultimate guide to IMPORTRANGE.

Understanding VLOOKUP

The VLOOKUP function searches across a range, one row at a time. If it finds a specific value in the first column, it returns a value in the same row from a specified column. As its name indicates, it’s commonly used to look up data.

It takes four arguments:

• search_key: the value to match.
• range: the cell coordinates to search.
• index: the column offset that indicates which values to return.
• [is_sorted]: whether the data is sorted. If unsure, set this to “FALSE” for good measure.

Check out our deep dive into this function.

Combining VLOOKUP and IMPORTRANGE

Using these two functions together allows us to apply VLOOKUP between two Google Sheets. Something that would be impossible otherwise.

The idea is simple, we just have to use IMPORTRANGE as the VLOOKUP range. Below is the basic syntax for this combination.

Let’s take a look at what that formula would look like with real information.

This approach is good for importing small data sets. However, if working with large spreadsheets, it’s better to use VLOOKUP on the source spreadsheet and import the result.

Below, you have step-by-step instructions on how to get values from a different spreadsheet using VLOOKUP and IMPORTRANGE.

Copy the source URL and cell range

The source file used in the examples contains customer information, including their ‘customer_id’. The destination file contains information on transactions identified by ‘customer_id’. This allows me to import data from the source file using ‘customer_id’ as the search key.

1. Open both the source and destination files in Sheets.

2. From the source file, copy the URL.

3. Paste the URL into the destination file.

4. Go back to the source file and select the cell range. You can copy the cell range reference from the top-left corner, as shown below.

5. Paste the cell range reference into the destination file. I won’t specify the sheet in this case because I’m only using “Sheet1”.

Add the VLOOKUP function

1. In the cell where you want the return value, start typing the VLOOKUP formula.

2. For the 1st argument, select the search key.

3. For the 2nd argument, add the IMPORTRANGE formula, referring to the cells where you copied the URL and cell range.

4. Add the column index, enter FALSE as the last argument to get an exact match, and close the parenthesis.

5. Press enter to see the result.

Approve access between the two files

If you’re working with files that have never been connected before, you’ll see an error message instead of the value when you press ‘Enter’. To see the actual value, click the cell with the #REF! error, then click Allow access.

VLOOKUP with IMPORTRANGE for multiple search values

One VLOOKUP returns one value. To fill a whole column, you can copy the formula down with autofill, or wrap it in ARRAYFORMULA so a single formula covers every row. ARRAY_CONSTRAIN then removes the errors that ARRAYFORMULA returns for empty rows.

Copy the formula with autofill

For the sake of simplicity, I’ll build on the example used in the previous section.

1. Add the VLOOKUP formula and select the search key as the 1st argument, using absolute referencing for the column.

2. Use IMPORTRANGE as the 2nd argument, with the source URL and cell range reference as the arguments. Use absolute referencing for both columns and rows.

3. Specify the index for the column with the result you want, and enter FALSE as the 4th argument for an exact match.

4. Press enter to see the result.

5. Double-click on the fill handle circled in red below.

6. To import values from the column to the right, grab the fill handle shown in the screenshot above and drag right.

7. Double-click to modify the formula to import from the 3rd column instead of the 2nd.

8. Press “Enter” to see the result. Google Sheets will automatically suggest Autofill, but if not, you can double-click on the fill handle again.

Going a step further with ARRAYFORMULA

The VLOOKUP can only be applied to one search key at a time. That means you would have to copy-paste it across necessary cells. To avoid this, we can add ARRAYFORMULA. 

The formula below is an improved version. It includes this other function and provides a range for the VLOOKUP search key.

The steps below build the same kind of formula for the customer data from the autofill example.

1. In the cell where you want the result, start with the ARRAYFORMULA function.

2. Add the VLOOKUP formula and select the range of values you want to look up.

3. Add IMPORTRANGE, using absolute referencing for the cells.

4. Specify the index for the column with the result you want, and enter FALSE as the last argument to get an exact match.

5. Press enter to see the result. If you see an error, remember to approve access between the two Sheets files.

6. To import the values from the next column, grab the fill handle and drag the formula right. Double-click to edit the column index.

Unfortunately, an open-ended range like A3:A also introduces an issue: the arrayformula returns a series of errors if information is missing.

Cleaning up with ARRAY_CONSTRAIN

The errors introduced by the last modification can be fixed by constraining the number of rows in the array. For that we will use ARRAY_CONSTRAIN to add this limitation. Again, the modifications are in bold.

The only significant change in this formula is the use of COUNTA for the number of rows. This function will count all the cells containing values that VLOOKUP can try to match.

And that’s the final iteration of this formula, these final changes made it simpler and more efficient.

Common errors with VLOOKUP and IMPORTRANGE

• The first time you link two files, the formula returns #REF! until you click the cell and choose Allow access. If the error stays, make sure you have at least view access to the source spreadsheet.
• #N/A means VLOOKUP didn’t find the search key in the first column of the imported range. Check that the range starts with the column that holds your keys, and look for extra spaces in the values.
• A #REF! error that mentions an out of bounds range means the index is larger than the number of columns you import. With the range follow_up!A:B, the index can only be 1 or 2.
• Large imports slow a spreadsheet down. If yours lags, run VLOOKUP in the source spreadsheet and import only the results.

For errors that come from IMPORTRANGE itself, see how to fix IMPORTRANGE not working.

Common use cases

Using both VLOOKUP and IMPORTRANGE can significantly enhance your data management. Here are some use cases that highlight how this combination could be useful.

Employee Information

• Case: Human Resources keeps employee records in one spreadsheet, but each department has separate project assignment sheets.
• Solution: Import employee records into department-specific sheets, then match project assignments with employee details like names and positions.

Inventory Management

• Case: Your company has several warehouses, each maintaining its own inventory spreadsheet.
• Solution: Import data from each warehouse’s sheet into a master inventory tracker, find stock levels and item details across all locations.

Financial Analysis

• Case: You manage multiple client accounts, each with its own financial records spreadsheet.
• Solution: Import and aggregate financial data from all client sheets into one. Afterwards, look up financial metrics to analyze (for example, total revenue, expenses, and profit margins per client).

With the power of VLOOKUP and IMPORTRANGE on your side, you can manage and analyze data across multiple spreadsheets.

Using Sheetgo to transfer and process data

Unfortunately relying heavily on both of these functions can be problematic. Importrange breaks often and slows down spreadsheets.

We have a solution: Sheetgo, an AI-powered data automation platform. Its connections can transfer data like IMPORTRANGE and process it like VLOOKUP. But it’s more efficient, as it allows you to configure when to transfer the data. It also gives you a lot of processing options and allows you to introduce forms to gather data.

Since Sheetgo is a no-code tool you can forget about using and maintaining all these complicated functions. Just set up your connections and keep important data close at hand.

Cut out data handling tasks and free up time for more important tasks, try out Sheetgo today.

Other functions to pair with IMPORTRANGE

When you need every row that meets a condition rather than one value per key, see how to combine QUERY and IMPORTRANGE or how to filter IMPORTRANGE. If you’re new to lookups across files, our guide on how to VLOOKUP between two Google Sheets starts with a lookup between two tabs of the same file.