VLOOKUP with IMPORTRANGE in Google Sheets | Layer Blog (2024)

The VLOOKUP function is very useful if you’re looking up specific values within a single Google Sheet file, but can you use it to look up values in a different file? Fortunately, IMPORTRANGE can be combined with VLOOKUP to look up values from a different spreadsheet. You can also combine the functionality of ARRAYFORMULA to look up a range of values.

In this article, you will learn how to use VLOOKUP with IMPORTRANGE to look up specific values in your source sheet and import the return value to your destination sheet. Additionally, you will learn two different methods you can use to look up multiple values. You have step-by-step instructions, as well as different examples using a single search key and multiple search keys. Finally, you will also learn about the limitations of using IMPORTRANGE and how they can be avoided by using Sheetgo.

If you want to learn more about the IMPORTRANGE function or how to use it with the QUERY function to search based on conditions instead of specific values, check out the following guides:

VLOOKUP and IMPORTRANGE Syntax

Before combining the two, it’s a good idea to review the syntax and arguments for each function.

If you want to use multiple search keys, you can wrap the ARRAYFORMULA function around VLOOKUP. This makes it possible to select an array of values as the search_key argument and get an array of values in return.

How to VLOOKUP with IMPORTRANGE in Google Sheets?

Now that you know the syntax for these functions, it’s time to learn how to use them together. Below, you have step-by-step instructions on how to get values from a different spreadsheet using VLOOKUP and IMPORTRANGE.

Copy Source URL and Cell Range Reference

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.

VLOOKUP with IMPORTRANGE in Google Sheets – Source File

VLOOKUP with IMPORTRANGE in Google Sheets – Destination File

2. From the source file, copy the URL.

VLOOKUP with IMPORTRANGE in Google Sheets – Copy Source File URL

3. Paste the URL into the destination file.

VLOOKUP with IMPORTRANGE in Google Sheets – Paste Source URL to 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.

VLOOKUP with IMPORTRANGE in Google Sheets – Select and Copy Source Cell Range

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

VLOOKUP with IMPORTRANGE in Google Sheets – Paste Cell Range Reference to Destination File

Add VLOOKUP function

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

VLOOKUP with IMPORTRANGE in Google Sheets – Add VLOOKUP Formula

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

VLOOKUP with IMPORTRANGE in Google Sheets – VLOOKUP 1st Argument

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

VLOOKUP with IMPORTRANGE in Google Sheets – VLOOKUP 2nd Argument

4. Add column index and close the parenthesis. Leave the last argument blank to get an exact match.

VLOOKUP with IMPORTRANGE in Google Sheets – VLOOKUP 3rd Argument

5. Press enter to see the result.

VLOOKUP with IMPORTRANGE in Google Sheets – VLOOKUP Result

Approve Access Between Sheets

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, you will have to approve access between sheets.

VLOOKUP with IMPORTRANGE in Google Sheets – Approve Access Between Sheets

Now let’s take a look at some examples of how to use the functions to import multiple values.

How To Use IMPORTRANGE Function In Google Sheets?

Google Sheets allows you to import and link a specific range of cells from another spreadsheet. Here’s how to use the IMPORTRANGE function in Google Sheets

VLOOKUP with IMPORTRANGE and Autofill for Mulitple Search Values

Now that you know how to import a single value, let’s see how to import multiple values. The examples below show how you can do this using two different methods: using autofill to copy the formula and using the ARRAYFORMULA function.

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.

VLOOKUP with IMPORTRANGE in Google Sheets – VLOOKUP 1st Argument

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.

VLOOKUP with IMPORTRANGE in Google Sheets – VLOOKUP 2nd Argument

3. Specify the index for the column with the result you want, and leave the 4th argument blank for an exact match.

VLOOKUP with IMPORTRANGE in Google Sheets – VLOOKUP 3rd Argument 2

4. Press enter to see the result.

VLOOKUP with IMPORTRANGE in Google Sheets – VLOOKUP Result

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

VLOOKUP with IMPORTRANGE in Google Sheets – Use Auto-fill

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

VLOOKUP with IMPORTRANGE in Google Sheets – Drag Formula Right

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

VLOOKUP with IMPORTRANGE in Google Sheets – Modify Index

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.

VLOOKUP with IMPORTRANGE in Google Sheets – Multiple Results from Multiple Columns

VLOOKUP with IMPORTRANGE and ARRAYFORMULA for Multiple Search Values

Using data from the previous example, I will now use VLOOKUP and IMPORTRANGE to look up multiple values. However, instead of Autofill, I will use the ARRAYFORMULA function so VLOOKUP can process the arrays.

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

VLOOKUP with IMPORTRANGE in Google Sheets – Add ARRAYFORMULA

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

VLOOKUP with IMPORTRANGE in Google Sheets – VLOOKUP 1st Argument

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

VLOOKUP with IMPORTRANGE in Google Sheets – VLOOKUP 2nd Argument

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

VLOOKUP with IMPORTRANGE in Google Sheets – VLOOKUP 3rd Argument

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

VLOOKUP with IMPORTRANGE in Google Sheets – VLOOKUP Array Results

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.

VLOOKUP with IMPORTRANGE in Google Sheets – Drag Formula Right and Modify Index

A no-hassle alternative to IMPORTRANGE

Now that you know how to use IMPORTRANGE with VLOOKUP in Google Sheets, you may be wondering how scalable this method is. If you’re dealing with more than a couple of files, it can get messy very quickly.

With Sheetgo, you can say goodbye to complex formulas and time-consuming errors and import your data with just a few clicks. Additionally, you can transfer the formatting from the source file, so you won’t have to waste any time fixing it in the destination file. Finally, you will also be able to keep the source file private, allowing you full control over who has access to what data.

Conclusion

As you have seen, you can look up values in a different sheet by combining VLOOKUP and IMPORTRANGE. If you need to search for multiple values, you can use the ARRAYFORMULA function to look up multiple values simultaneously. VLOOKUP is a very useful function if you need to import specific values based on simple criteria. By combining it with other formulas, you can expand its functionality. However, if you have complex criteria and need to manipulate or summarize the data, the QUERY function is the better choice.

You now know how to look up and import values from different spreadsheets using VLOOKUP and IMPORTRANGE in Google Sheets. You know how to look up single and multiple search values to return a value or an array of values. You also know that Sheetgo is a great alternative to IMPORTRANGE, overcoming its limitations and offering additional advanced features. Sign up for Sheetgo and start saving time today.

To learn more about the VLOOKUP function in Microsoft Excel and Google Sheets or the IMPORTRANGE function in Google Sheets, have a look at the articles below.