Don’t forget to share this post

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.

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 posts:

Layer google sheets add on offer
Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

GET STARTED FOR FREE

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 From Another Google Sheet or Workbook Examples
How to VLOOKUP From Another Google Sheet or Workbook (+ Examples)

Sometimes our data may be spread out among different Google sheets or workbooks. Here's how to VLOOKUP from another sheet or workbook

READ MORE

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.
VLOOKUP with IMPORTRANGE in Google Sheets Source File
VLOOKUP with IMPORTRANGE in Google Sheets - Source File
VLOOKUP with IMPORTRANGE in Google Sheets Destination File
VLOOKUP with IMPORTRANGE in Google Sheets - Destination File
  1. 2. From the source file, copy the URL.
VLOOKUP with IMPORTRANGE in Google Sheets Copy Source File URL
VLOOKUP with IMPORTRANGE in Google Sheets - Copy Source File URL
  1. 3. Paste the URL into the destination file.
VLOOKUP with IMPORTRANGE in Google Sheets Paste Source URL to Destination File
VLOOKUP with IMPORTRANGE in Google Sheets - Paste Source URL to 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.
VLOOKUP with IMPORTRANGE in Google Sheets Select and Copy Source Cell Range
VLOOKUP with IMPORTRANGE in Google Sheets - Select and Copy Source Cell Range
  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”.
VLOOKUP with IMPORTRANGE in Google Sheets Paste Cell Range Reference to Destination File
VLOOKUP with IMPORTRANGE in Google Sheets - Paste Cell Range Reference to Destination File

Add VLOOKUP function

  1. 1. In the cell where you want the return value, start typing the VLOOKUP formula.
VLOOKUP with IMPORTRANGE in Google Sheets Add VLOOKUP Formula
VLOOKUP with IMPORTRANGE in Google Sheets - Add VLOOKUP Formula
  1. 2. For the 1st argument, select the search key.
VLOOKUP with IMPORTRANGE in Google Sheets VLOOKUP 1st Argument
VLOOKUP with IMPORTRANGE in Google Sheets - VLOOKUP 1st Argument
  1. 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
VLOOKUP with IMPORTRANGE in Google Sheets - VLOOKUP 2nd Argument
  1. 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
VLOOKUP with IMPORTRANGE in Google Sheets - VLOOKUP 3rd Argument
  1. 5. Press enter to see the result.
VLOOKUP with IMPORTRANGE in Google Sheets VLOOKUP 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
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

READ MORE
How To Use IMPORTRANGE Function In Google Sheets

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.
VLOOKUP with IMPORTRANGE in Google Sheets VLOOKUP 1st Argument 2
VLOOKUP with IMPORTRANGE in Google Sheets - VLOOKUP 1st Argument
  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.
VLOOKUP with IMPORTRANGE in Google Sheets VLOOKUP 2nd Argument 2
VLOOKUP with IMPORTRANGE in Google Sheets - VLOOKUP 2nd Argument
  1. 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
VLOOKUP with IMPORTRANGE in Google Sheets - VLOOKUP 3rd Argument 2
  1. 4. Press enter to see the result.
VLOOKUP with IMPORTRANGE in Google Sheets VLOOKUP Result 2
VLOOKUP with IMPORTRANGE in Google Sheets - VLOOKUP Result
  1. 5. Double-click on the fill handle circled in red below.
VLOOKUP with IMPORTRANGE in Google Sheets Use Auto fill
VLOOKUP with IMPORTRANGE in Google Sheets - Use Auto-fill
  1. 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
VLOOKUP with IMPORTRANGE in Google Sheets - Drag Formula Right
  1. 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
VLOOKUP with IMPORTRANGE in Google Sheets - Modify Index
  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 in Google Sheets Multiple Results from Multiple Columns
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. 1. In the cell where you want the result, start with the ARRAYFORMULA function.
VLOOKUP with IMPORTRANGE in Google Sheets Add ARRAYFORMULA
VLOOKUP with IMPORTRANGE in Google Sheets - Add ARRAYFORMULA
  1. 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
VLOOKUP with IMPORTRANGE in Google Sheets - VLOOKUP 1st Argument
  1. 3. Add IMPORTRANGE, using absolute referencing for the cells.
VLOOKUP with IMPORTRANGE in Google Sheets VLOOKUP 2nd Argument 3
VLOOKUP with IMPORTRANGE in Google Sheets - VLOOKUP 2nd Argument
  1. 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 3
VLOOKUP with IMPORTRANGE in Google Sheets - VLOOKUP 3rd Argument
  1. 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
VLOOKUP with IMPORTRANGE in Google Sheets - VLOOKUP Array Results
  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.
VLOOKUP with IMPORTRANGE in Google Sheets Drag Formula Right and Modify Index
VLOOKUP with IMPORTRANGE in Google Sheets - Drag Formula Right and Modify Index

How to import your data into Google Sheets?

Layer is an add-on that equips you with the tools to increase efficiency and data quality in your processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds. See how it works.

Using Layer, you can:

  • Share & Collaborate: Automate your data collection and validation through user controls.
  • Automate & Schedule: Schedule recurring data collection and distribution tasks.
  • Integrate & Sync: Connect to your tech stack and sync all your data in one place.
  • Visualize & Report: Generate and share reports with real-time data and actionable decisions.

Limited Time Offer: Install the Layer Google Sheets Add-On today and Get Free Access to all the paid features, so you can start managing, automating, and scaling your processes on top of Google Sheets!

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

Layer google sheets add on offer
Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

GET STARTED FOR FREE
Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Nov 22 2022, Updated Nov 30 2022