VLOOKUP to Another Workbook in Google Sheets

To use VLOOKUP with data in another Google Sheets workbook, wrap the source range in IMPORTRANGE, then use VLOOKUP to search it. For example, with a lookup SKU in A2 and source SKUs and prices in the other workbook’s columns A and C, use =VLOOKUP(A2,IMPORTRANGE("https://docs.google.com/spreadsheets/d/your-spreadsheet-id/edit","Products!A2:C"),3,FALSE) and replace the sample URL and tab name with your own.

Why a second function is needed

In Google Sheets, a workbook is a spreadsheet file, and each file can contain multiple tabs. VLOOKUP can search a table range, but a range reference by itself normally points to cells in the current file. When the table lives in a different spreadsheet file, IMPORTRANGE connects to that source and brings the specified range into the formula.

The functions have separate jobs: IMPORTRANGE identifies the external spreadsheet and the tab range to import; VLOOKUP finds a value in the first column of that imported range and returns a value from a column to its right. The lookup key is often a product code, customer ID, or other identifier. A reliable unique key is preferable to a name that may appear more than once.

Set up the source and destination

Before writing the formula, identify the source spreadsheet URL, the exact source tab name, and the columns that contain the lookup key and the result. For example, imagine a source tab named Products with SKU codes in column A, product names in B, and prices in C. In the destination spreadsheet, A2 contains the SKU to find. The source range A2:C excludes the header row.

Open the source spreadsheet in a browser and copy its URL from the address bar.
Check the tab name and confirm that the key is in the leftmost column of the range you plan to import.
In the destination file, choose a cell for the formula and replace the example URL, tab name, and lookup-cell reference as needed.
Make sure the source file is shared with the Google account using the destination spreadsheet. The first connection may also require an authorization step.

The URL can be the full address of the spreadsheet or its spreadsheet ID, provided it is entered as text in the formula. The ID is the section after /d/ in a standard Google Sheets URL. Use the tab name in the range string, not the file name shown in Drive.

Build the formula and read its parts

With the lookup SKU in A2 and the source Products tab arranged with SKUs in A, names in B, and prices in C, the formula below searches the imported A2:C range and returns its third column:

=VLOOKUP(A2,IMPORTRANGE("https://docs.google.com/spreadsheets/d/your-spreadsheet-id/edit","Products!A2:C"),3,FALSE)

In this setup, if A2 contains SKU-104 and the matching source row has 24.99 in column C, the result is 24.99. The arguments mean: look for A2; use the imported range as the table; return the third column of that range; and use FALSE for an exact match. VLOOKUP’s column number is counted from the start of the selected range, not from the whole spreadsheet. If the imported range begins at B, then its first column is B.

FALSE is important for identifiers and most ordinary lookups. It tells VLOOKUP to find an exact match rather than relying on a sorted first column for an approximate match. Exact matching still requires the lookup value and source key to agree; for instance, the text “0017” may not behave like the number 17.

Authorize IMPORTRANGE access

A new connection between two spreadsheets can initially show a loading message or an access error. A practical way to authorize it is to enter IMPORTRANGE by itself in an unused cell in the destination spreadsheet, then use the prompt to allow access. For example, with F1 available in the destination file and the same source URL and Products tab, enter:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/your-spreadsheet-id/edit","Products!A2:C")

After the imported rows appear, the VLOOKUP formula can use that connection. You can keep the test import if it is useful, or remove it after checking that the main formula works. If there is no authorization prompt, first confirm that the account currently signed in has permission to open the source spreadsheet. A formula cannot bypass the source file’s access controls.

Tab names, ranges, and exact matches

Tab names with spaces or punctuation need careful quoting inside the range string. For example, if the source tab is named Product Catalog, the range portion is written as 'Product Catalog'!A2:C. With the lookup key in A2 and that source tab’s SKU, name, and price in columns A:C, the full formula is:

=VLOOKUP(A2,IMPORTRANGE("https://docs.google.com/spreadsheets/d/your-spreadsheet-id/edit","'Product Catalog'!A2:C"),3,FALSE)

Those single quotes are part of the range notation; the outer double quotes mark the range string in the formula. If a tab name has no spaces, the single quotes are generally unnecessary. Verify spelling and spaces exactly, since a misspelled tab name or range can produce a reference error.

The table range must begin with the column containing the lookup key. VLOOKUP searches only the first column of its table array, and it cannot return a value to the left of that key. If the SKU is in column C and the price is in column A, either choose a different function or arrange the imported range so the key comes first and the desired result is to its right.

Common errors and how to fix them

#REF! or an access message: Confirm that the source URL is correct and that the signed-in account can open the source. For a new connection, use IMPORTRANGE alone once and accept the access prompt if it appears.
Could not find range or sheet: Check the tab name, punctuation, and range boundaries. A tab named Product Catalog is not the same as Products.
#N/A: The exact lookup value may not exist. Check for leading or trailing spaces, inconsistent capitalization where relevant, different data types, or a key that was omitted from the imported range.
Wrong value returned: Check that the key is the first column of the imported range, that the column index is counted from that range’s first column, and that the fourth argument is FALSE.
Formula parse error: Check quotation marks and parentheses. In some spreadsheet locales, the formula separator may be a semicolon rather than a comma; use the separator expected by that file’s locale.

For example, if a source code is stored as the number 17 but the destination contains text “017,” an exact lookup may not find it. Normalize the identifiers in both files before relying on the result. Likewise, a duplicate key is not an error: VLOOKUP returns the first matching row in the table range, so duplicate IDs can make a result look unexpected.

Handling missing values and keeping the sheet manageable

If a missing key should display a friendly message rather than #N/A, put VLOOKUP inside IFNA. With the lookup key in A2 and the same Products A2:C source range, the formula below returns “Not found” when there is no match; when the key exists and its price is 24.99, it returns 24.99:

=IFNA(VLOOKUP(A2,IMPORTRANGE("https://docs.google.com/spreadsheets/d/your-spreadsheet-id/edit","Products!A2:C"),3,FALSE),"Not found")

IFNA is narrower than IFERROR: it handles the not-found result, while other problems such as a broken reference can remain visible for troubleshooting. That can be useful while setting up a connection, because hiding every error may make a permissions or range problem harder to spot.

For many lookup rows, avoid unnecessarily importing huge ranges or repeating complex references more than necessary. Specify only the columns and rows the task needs, and keep the source key column consistent. IMPORTRANGE data updates from the source, but changes may not appear instantly; very large imports or many dependent formulas can also make a spreadsheet slower to recalculate.

Google Sheets versus Excel external lookups

Google Sheets uses IMPORTRANGE to connect a formula to a range in another spreadsheet file. Excel uses a different external-workbook reference system; an Excel formula that refers to a workbook path is not the syntax for Google Sheets. If a workbook is converted or copied between the programs, review external references rather than assuming they transfer unchanged.

In Sheets, the destination spreadsheet must have an authorized connection to the source, and the formula identifies the source URL and range as text. This makes the source file’s sharing settings part of the setup. It is a useful distinction when collaborating across accounts or moving a spreadsheet into a different Drive folder.

Access and sharing considerations

Only connect to a source spreadsheet you are permitted to use. People who can edit the destination spreadsheet may be able to use its authorized IMPORTRANGE connection to retrieve other ranges from that source. Do not treat a formula that imports only selected columns as a security boundary. If the source contains confidential information, limit who can edit the destination and review the source and destination sharing settings.

Also consider whether the destination needs a live connection. For a one-time report, copying only the necessary values may be simpler, but copied values will not update when the source changes. A live formula is useful for recurring updates; a static copy can be appropriate when a fixed snapshot is the requirement. Choose based on update needs and access requirements, not just formula convenience.

Frequently Asked Questions

Can VLOOKUP pull data from another Google Sheets file?
Yes. Use IMPORTRANGE as VLOOKUP’s table range, authorize the connection when prompted, and ensure the lookup key is the first column of the imported range.

Do both spreadsheets need to be open?
No. The source does not need to remain open in another browser tab, but the account using the destination formula must have access to the source and the connection must be authorized.

Can VLOOKUP return a value to the left of the lookup column?
No. VLOOKUP searches the first column of its table range and returns a column to its right. Use a function or range arrangement that supports the desired direction if the return column is on the left.

Why does my formula return #N/A when the code looks identical?
Check whether one value is text and the other is numeric, whether either contains extra spaces, and whether the source range includes the row with that key. Exact match does not make differently stored values identical.

Will the result update when the source changes?
IMPORTRANGE is designed to bring in data from the source, but updates may take time to propagate. If you need a fixed snapshot, copy and paste values instead; those copied values will not stay linked.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.