VLOOKUP with IMPORTRANGE in Google Sheets

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:

• How To Use IMPORTRANGE Function In Google Sheets
• QUERY with IMPORTRANGE in Google Sheets

VLOOKUP and IMPORTRANGE Syntax

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

=IMPORTRANGE(“spreadsheet_url”, “range_string”)

• spreadsheet_url: the link to the spreadsheet containing the data you want to import. It should be enclosed in quotation marks or be a reference to a cell containing the URL.
• range_string: the format is “[sheet_name!]range", where sheet name is optional. The function will import from the first sheet if no sheet name is provided.

=VLOOKUP(search_key, range, index, [is_sorted])

• search_key: the value you want to search for in the first column of the range.
• range: the reference to the cell range that should be considered for the search. This is where you can insert IMPORTRANGE to search data contained in another spreadsheet.
• index: the index of the column containing the return value. The first column’s index is 1, not 0.
• is_sorted*: this argument is optional. For an exact match, use “FALSE” or leave it blank. If you want an approximate match, type “TRUE”, but remember that the values in that column must be sorted.

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. 1. Open both the source and destination files in Sheets.

1. 2. From the source file, copy the URL.

1. 3. Paste the URL into the destination file.

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

1. 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 VLOOKUP function

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

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

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

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

1. 5. Press enter to see the 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.

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

Examples: VLOOKUP with IMPORTRANGE 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. 1. Add the VLOOKUP formula and select the search key as the 1st argument, using absolute referencing for the column.

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

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

1. 4. Press enter to see the result.

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

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

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

1. 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 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. 1. In the cell where you want the result, start with the ARRAYFORMULA function.

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

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

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

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

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

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.

• How to VLOOKUP in Excel with Two Spreadsheets
• How to VLOOKUP From Another Google Sheet or Workbook (+ Examples)
• How To Use IMPORTRANGE Function In Google Sheets
• QUERY with IMPORTRANGE in Google Sheets