XLOOKUP in Google Sheets From Another Sheet: A Guide

Yes. In Google Sheets, XLOOKUP can search a range on another tab in the same spreadsheet: if the value to find is in A2 and product codes and prices are on a tab named Inventory, use =XLOOKUP(A2,'Inventory'!A:A,'Inventory'!C:C,"Not found"). The formula searches column A on Inventory and returns the matching value from column C.

What XLOOKUP does across tabs

A spreadsheet tab is a sheet within the same Google Sheets file. XLOOKUP can refer to ranges on that tab just as it refers to ranges on the current tab; the sheet name and an exclamation mark identify the source. This is useful when one tab holds a clean reference list and another tab holds transactions, orders, or questions that need to be matched against it.

For example, an Inventory tab might hold product codes, names, and prices, while an Orders tab lists codes that need a price. Putting the lookup formula on Orders lets you keep the source list separate while displaying the result beside each order. The formula does not move or copy the inventory data: it returns a value from the referenced range.

This is different from looking up data in a separate spreadsheet file. A tab in the same file can be referenced directly. Data in another file generally needs to be brought into the current file with IMPORTRANGE before you use XLOOKUP on it.

Set up the source range and formula

First confirm which column contains the value you will search for and which column contains the value you want back. The two ranges must correspond row by row. In this example, Inventory has product codes in A2:A500, product names in B2:B500, and unit prices in C2:C500. Orders has the code to search for in A2.

With the search code in Orders!A2, codes in Inventory!A2:A500, and prices in Inventory!C2:C500, enter this formula in Orders!B2:

=XLOOKUP(A2,'Inventory'!A2:A500,'Inventory'!C2:C500,"Not found")

If A2 contains P-104 and that code is on row 17 of Inventory, the formula returns the price from C17. If it is absent, the result is Not found. To fill the formula down for more orders, use the fill handle; the relative reference A2 changes to A3, A4, and so on. The Inventory ranges above are also relative, so for a formula copied down, lock those ranges to keep them fixed:

With the same layout and the formula in Orders!B2 to be copied down, use absolute references for the source ranges:

=XLOOKUP(A2,'Inventory'!$A$2:$A$500,'Inventory'!$C$2:$C$500,"Not found")

The dollar signs keep the lookup and return ranges from shifting as you fill down, while A2 stays relative so each row searches for its own code.

Understand the syntax and match behavior

Google Sheets uses the argument order XLOOKUP(search_key, lookup_range, result_range, missing_value, match_mode, search_mode). The last three arguments are optional. The search key is the value being sought; lookup_range contains possible matches; result_range contains the aligned values to return. The lookup and result ranges should be single rows or columns of the same length.

By default, XLOOKUP looks for an exact match and searches from the first item toward the last. That makes it a good fit for IDs, product codes, email addresses, and other values that should match exactly. If the same key occurs more than once, the default returns the first matching row in the search direction.

The optional missing_value argument controls what appears when there is no match. It can be readable text such as Not found, or an empty string represented by two quotation marks. Leaving it out makes an unmatched search return an error. A custom message often makes a report easier to understand, but do not use a blank result if readers need to distinguish a missing key from a matched row whose return cell is genuinely blank.

Match mode can change the search: 0 is exact, 1 is exact or next larger, -1 is exact or next smaller, and 2 enables wildcard matching. Search mode 1 searches from first to last, while -1 searches from last to first. Search modes 2 and -2 use binary search and require the lookup range to be sorted in the corresponding ascending or descending order; using them with unsorted data can produce incorrect results.

Useful variations for everyday lookups

To return a product name rather than its price, point the result range at the name column. With the same Orders and Inventory layout, where Orders!A2 contains the code and Inventory column B contains product names, use:

=XLOOKUP(A2,'Inventory'!$A$2:$A$500,'Inventory'!$B$2:$B$500,"No product code")

For a wildcard search, match mode 2 recognizes the asterisk as a stand-in for any number of characters and the question mark as a stand-in for one character. With a partial text key in Orders!A2 and descriptions in Inventory!B2:B500, this searches for a description containing that text:

=XLOOKUP("*"&A2&"*",'Inventory'!$B$2:$B$500,'Inventory'!$C$2:$C$500,"No match",2)

Here, the result is the value from column C on the first row whose column B description contains the text in A2. Wildcards are most appropriate for text searches; use exact matching for unique codes so a partial resemblance does not return the wrong record.

If you deliberately want the last matching duplicate instead of the first, set search mode to -1. With codes in Inventory!A2:A500 and prices in C2:C500, the following searches from the bottom upward:

=XLOOKUP(A2,'Inventory'!$A$2:$A$500,'Inventory'!$C$2:$C$500,"Not found",0,-1)

This can help when a list is organized chronologically and the latest entry is at the bottom. It does not determine which duplicate is newest on its own; that depends on how the source data is ordered.

When the source is in a different spreadsheet

A direct reference such as ‘Inventory’!A:A only reaches a tab in the current file. If the source is a separate Google Sheets file, use IMPORTRANGE to connect the files. One practical approach is to create an Imported tab, bring in the source columns once, then run XLOOKUP against that local tab. The first time you connect the files, Google Sheets may ask you to allow access.

With the source file URL or spreadsheet ID substituted for the placeholder, and the source tab named Inventory with data in columns A through C, enter this in Imported!A1:

=IMPORTRANGE("paste_source_spreadsheet_url_here","Inventory!A:C")

After access is allowed and the data appears in Imported, with a search key in Orders!A2, imported codes in Imported!A:A, and imported prices in Imported!C:C, use:

=XLOOKUP(A2,Imported!A:A,Imported!C:C,"Not found")

Importing once into a helper tab is often easier to maintain than repeating IMPORTRANGE inside every lookup formula. It also makes it easier to inspect the imported data if a match fails. The imported values reflect the source through the connection, so access permissions and changes to the source file can affect whether the lookup continues to work.

Fix common errors and unexpected results

Sheet name errors: A tab name with spaces or punctuation should be enclosed in single quotation marks, as in ‘Product List’!A2:A100. A simple name such as Inventory can also be quoted. Check spelling and punctuation against the actual tab label.
Different range sizes: The lookup and result ranges need to align. Searching A2:A100 and returning from C2:C99 can cause an error because the ranges are not the same length. Make both ranges start and end on corresponding rows.
Numbers stored as text: A code that looks like 104 may be stored as text in one place and as a number in another. Formatting alone may not change the underlying value. Compare the source and search values, and standardize how the data is entered before relying on the match.
Extra spaces or inconsistent characters: A leading space, trailing space, or visually similar character can prevent an exact match. Clean or standardize source data when possible; avoid changing identifiers in ways that could make distinct values identical.
Unexpected duplicate result: Default search returns the first matching item. Check whether the key appears more than once and decide whether the first, last, or a different record is actually intended.
Unmatched result hidden by a blank: If the missing-value argument is empty text, an absent key looks like an empty return cell. Use a clear message during troubleshooting so you can tell that no match was found.

For larger sheets, bounded ranges such as A2:A5000 can be easier to audit than entire columns and may avoid unnecessary work. Make the bounds large enough for expected new records, and extend them when the source grows. In a shared sheet, also check that collaborators have access to the relevant tabs and, for IMPORTRANGE, that the connection has been authorized.

XLOOKUP compared with VLOOKUP and alternatives

VLOOKUP is familiar and remains useful, but it searches the first column of its selected table and returns a value from a column to its right using a column number. XLOOKUP takes the lookup range and result range separately, so the return column can be either to the left or the right of the search column. It also has a built-in missing-value argument and does not need a column index that might become outdated when columns are inserted.

INDEX and MATCH can also look up values and are worth knowing if you maintain older spreadsheet templates or need a familiar alternative. For a straightforward exact lookup in Google Sheets, XLOOKUP is often easier to read. If a sheet is shared with people using other spreadsheet programs or older versions, verify that their software supports the function before building a workflow around it; compatibility can depend on the application they open the file in.

Do not confuse XLOOKUP with a feature that searches for every occurrence. It returns a result for a matching position, not a list of all rows with that key. If you need all matching records rather than one value, use a filtering approach such as FILTER, or reconsider the table structure. Choose the tool based on whether the task is to retrieve one corresponding value or to display multiple qualifying rows.

Frequently Asked Questions

Can XLOOKUP search another tab in the same Google Sheets file?
Yes. Put the tab name, an exclamation mark, and the range in the lookup or result argument, such as ‘Inventory’!A2:A500. Use single quotation marks around tab names that contain spaces or punctuation.

Does XLOOKUP work between two different Google Sheets files?
Not through a direct tab reference. First use IMPORTRANGE to bring the needed data into the current file, allow access when prompted, and then point XLOOKUP at the imported range.

Why does XLOOKUP say it cannot find a value that appears in the list?
Check for spaces, different underlying data types, misspelled sheet names, and misaligned ranges. A number stored as text or a code with an invisible trailing space may look identical on screen but fail an exact match.

How do I return a custom message when there is no match?
Add the message as the fourth argument, for example =XLOOKUP(A2,'Inventory'!A2:A500,'Inventory'!C2:C500,"Not found"). That argument is the missing-value result, not a general replacement for every possible formula error.

Does XLOOKUP return the first or last duplicate?
By default it returns the first match in the range, searching from the beginning. Set the sixth argument, search_mode, to -1 to search from the end and return the last matching entry.

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.