XLOOKUP in Google Sheets From Another Workbook

Yes. To use XLOOKUP in Google Sheets with data in another spreadsheet file, wrap the source ranges in IMPORTRANGE. For example, =XLOOKUP(A2,IMPORTRANGE("source-URL","Products!A2:A500"),IMPORTRANGE("source-URL","Products!B2:B500"),"Not found") looks for the value in A2 in the source file’s Products column A and returns the matching value from column B. The first time, Google Sheets may ask you to allow access to the source file.

What “another workbook” means in Google Sheets

Google Sheets calls an individual file a spreadsheet, rather than a workbook, but people often use the terms interchangeably. A formula can refer directly to another tab in the same spreadsheet with a reference such as Products!A:B. A separate Google Sheets file is different: its ranges are not directly available to a formula in your current file. IMPORTRANGE brings a range across that file boundary, and XLOOKUP searches the imported data.

In this setup, XLOOKUP does the matching and returning; IMPORTRANGE connects the files. XLOOKUP searches a lookup range for a key and returns the value or row from a corresponding result range. It does not require the lookup column to be the leftmost column in the source data, and an exact match is the default. The source and destination files remain separate; the formula does not copy the source tab into your spreadsheet.

Set up the direct XLOOKUP formula

Open the destination spreadsheet and identify the cell containing the value you want to find, such as A2.
Copy the URL of the source Google Sheets file from your browser. You can use the full URL or its spreadsheet ID in the IMPORTRANGE argument.
Find the source tab and the columns containing the lookup keys and the values to return. In the example below, IDs are in A and names are in B on the Products tab.
Enter the formula in the destination cell, replace the sample URL with the source file’s URL, and adjust the tab name and row limits to fit your data.
If a permission prompt or #REF! message appears, select the cell and choose Allow access when prompted.

Assume the destination key is in A2; the source file has product IDs in Products!A2:A500 and product names in Products!B2:B500. Replace the URL text with the actual source spreadsheet URL. If A2 contains P-104 and that ID’s name in the source is Desk lamp, the result is Desk lamp.

=XLOOKUP(A2,IMPORTRANGE("https://docs.google.com/spreadsheets/d/PASTE_SOURCE_ID_HERE/edit","Products!A2:A500"),IMPORTRANGE("https://docs.google.com/spreadsheets/d/PASTE_SOURCE_ID_HERE/edit","Products!B2:B500"),"Not found")

The lookup and result ranges must line up: the first item in the lookup range corresponds to the first item in the result range, the second to the second, and so on. Use the same starting and ending rows for both. Bounded ranges such as rows 2 through 500 can also avoid importing far more data than the lookup needs. If your source tab is named Product List, put its name in the range string with single quotes, as in "'Product List'!A2:A500".

Connect the files with IMPORTRANGE

The first connection between a destination spreadsheet and a source spreadsheet needs authorization from a user who can access the source. A common way to establish it is to enter a small IMPORTRANGE formula in an empty cell first. Assume the source file contains the Products tab and you want its A and B columns imported starting at A1 of a tab named SourceData:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/PASTE_SOURCE_ID_HERE/edit","Products!A:B")

The formula imports the requested range; it does not create an independently editable copy of the source data. If a connection prompt appears, allow access. After the import succeeds, you can use a simpler local lookup on the imported tab. Assume the destination key remains in A2, and SourceData has imported IDs in column A and names in column B:

=XLOOKUP(A2,SourceData!A:A,SourceData!B:B,"Not found")

This two-step approach is often easier to inspect and maintain than repeating IMPORTRANGE in every lookup formula. It also makes the imported rows visible, so you can check whether a missing result is caused by the source data or by the lookup. Give the helper tab a clear name, and avoid putting unrelated content in the area where the imported range needs to expand.

Choose the right lookup and return ranges

Set the lookup range to the column that contains the search key, not necessarily the first column of the source sheet. Set the result range to the corresponding column or block you want returned. For example, if product IDs are in column C and prices are in column F, the lookup can search C and return F. If the source layout changes, update both ranges together so each key still lines up with its intended result.

For a return spanning several adjacent columns, XLOOKUP can return a row of results from a multi-column result range. Assume A2 contains an employee ID, SourceData column A contains IDs, and columns B through D contain department, location, and manager. Enter this in an otherwise empty destination area:

=XLOOKUP(A2,SourceData!A:A,SourceData!B:D,"Not found")

The matching row’s B:D values can fill cells to the right of the formula. Leave those cells empty so the returned results have room to appear. If you want only one result, specify a single result column instead.

Duplicates, missing values, and match behavior

By default, XLOOKUP looks for an exact match and returns the result associated with the first matching key it encounters. If the source contains duplicate IDs, that means the earlier matching row is returned—not necessarily the newest or most appropriate record. Clean up duplicate keys or choose a deliberate rule before relying on the output. XLOOKUP also has optional match and search settings, but exact matching is usually the sensible choice for IDs, email addresses, and other unique keys.

The fourth XLOOKUP argument lets you display a useful message when there is no match, as in "Not found" in the examples. That text is not the same as a formula error: it is the result you asked the formula to return when the key is absent. If you leave this argument out, a missing exact match normally produces #N/A. A message can make a report easier to read, but it can also hide the difference between a genuinely absent key and a data-quality problem, so consider whether blanks or a specific label are more appropriate for your workflow.

Common errors and practical fixes

#REF! with an access prompt: Authorize the connection using Allow access. Confirm that the signed-in Google account can open the source file. If access was revoked or the URL points to a different file, restore permission or correct the URL.
#REF! without a useful match: Check that the source spreadsheet URL is correct and the range string names a real tab and valid range. A renamed or deleted tab can break the reference.
#N/A or “Not found”: Check that the key exists in the imported lookup column. Look for leading or trailing spaces, different punctuation, or IDs stored inconsistently as text and numbers. A displayed value can look identical while its underlying value differs.
Unexpected result: Check for duplicate keys and verify that the result range starts and ends on the same rows as the lookup range. The first duplicate may be the one returned.
Formula parse error: Check quotation marks, commas, parentheses, and the spelling of tab names. Spreadsheet locale settings can affect which argument separator is accepted; if commas are rejected, check the file’s locale conventions.
Results do not appear across columns: If you return multiple columns, clear any occupied cells where the result needs to expand.

When comparing IDs, it can help to standardize the source data rather than repeatedly modifying the lookup formula. For instance, decide whether an ID such as 00127 is text with meaningful leading zeroes or the number 127. Converting one side without considering the other can create apparent mismatches or change the identifier itself.

Performance, access, and data handling

IMPORTRANGE is useful for connected spreadsheets, but it adds an external-data dependency. A large imported range or many separate import formulas can make a file slower to recalculate or harder to troubleshoot. Import only the columns and rows the task needs, and consider one helper import followed by local lookups when many destination cells use the same source data. Avoid using entire-column imports automatically if the source is large; choose a sensible range and expand it when needed.

Imported values may not update at the exact instant a change is made in the source. Google Sheets may need time to recalculate and refresh connected data. If a value seems stale, check the source first, then give the destination a moment to update and verify that the connection still has permission. For workflows that require tightly controlled refresh timing or a permanently fixed snapshot, an imported formula may not be the right data-transfer method.

Access matters too. People who can view the destination spreadsheet may be able to see the values that its formulas import, even if they do not have the same access to the source file. Share the destination and source deliberately, especially when the source contains sensitive information. IMPORTRANGE is a data-linking feature, not a method for bypassing a source file’s permissions.

When to use another approach

Use XLOOKUP with IMPORTRANGE when the records live in a separate Google Sheets file and you want a result tied to a key. If the data is already in another tab of the same file, a direct XLOOKUP between tabs is simpler and does not need IMPORTRANGE. If you need to bring in a whole table rather than retrieve a matching record, IMPORTRANGE alone may be enough. If the data should be combined once and then stay independent, copying and pasting values is a different choice, but it will not keep the destination synchronized with later source edits.

Google Sheets and Excel use different ways to connect separate files. Excel for Microsoft 365 can use workbook references in formulas when both workbooks are available and linked appropriately, but it does not use Google Sheets’ IMPORTRANGE function. Conversely, a Sheets formula using IMPORTRANGE is not a portable formula for Excel. If collaborators use both products, confirm where the live source data will reside and test the workflow in the application everyone needs to use.

Frequently Asked Questions

Can XLOOKUP in Google Sheets search another spreadsheet directly?
Not by naming a separate file in the XLOOKUP range alone. Use IMPORTRANGE to connect a source range, then search that range with XLOOKUP.

Do I need to approve IMPORTRANGE every time?
Usually, you authorize a connection between the destination and source spreadsheet once. You may need to address access again if the source changes, permissions are removed, or the formula refers to another file.

Can I use a spreadsheet ID instead of the full URL?
Yes. IMPORTRANGE accepts the source spreadsheet URL or its spreadsheet ID as its first argument. The URL is often easiest to copy directly from the browser.

Why does XLOOKUP say “Not found” when the value looks identical?
Check for spaces, different data types, or other hidden differences in the key. Also confirm the formula searches the correct source column and that the relevant row is inside the imported range.

Can XLOOKUP return more than one column?
Yes. Use a multi-column result range, such as B:D, and leave enough empty cells to the right of the formula for the returned values to display.

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.