XLOOKUP in Google Sheets: Search Across Two Sheets

To look up a value on another Google Sheets tab, put the value to find in A2 and use =XLOOKUP(A2,Inventory!A:A,Inventory!B:B,"Not found"). If the match is on the Inventory tab, this searches column A there and returns the corresponding value from column B; for example, P-104 can return Desk lamp.

What XLOOKUP does in Google Sheets

XLOOKUP finds a search key in one range and returns the value at the same position in another range. The lookup range and result range can be on different tabs from the formula, so a common setup is to keep input data on one sheet and a reference table on another. You do not need to copy the reference table onto the current tab first.

In the opening example, A2 contains a product code, Inventory!A:A contains product codes, and Inventory!B:B contains product names. XLOOKUP searches for the code from A2 and returns the name from the matching row. The formula’s fourth argument, “Not found”, is an optional message to show when the code is absent rather than the usual no-match error.

This is a one-way lookup: it finds a key in the range you specify and returns a corresponding result. The lookup column does not have to be to the left of the return column, unlike the usual VLOOKUP arrangement. It also does not require a column-number argument, which makes it easier to understand when your table changes.

How to reference a second sheet

A cross-sheet reference uses the tab name, an exclamation mark, and the range. For a tab named Inventory, the reference to its first column is Inventory!A:A. The exclamation mark separates the sheet name from the cell or range; it is not part of the tab name.

If the tab name contains spaces or punctuation, put the name in single quotes. For example, a tab named Product List uses ‘Product List’!A:A. The single quotes are part of the formula syntax, not characters to add to the actual tab name. Sheet names are not case-sensitive in ordinary references.

To enter the formula, click the cell where you want the answer, type the formula, and press Enter. Confirm that the tab name and both ranges are correct. If you use a fixed range such as A2:A500 for the lookup range, use the corresponding rows in the result range, such as B2:B500. The two ranges need to line up so each key is paired with its intended result.

Search two sheets in order

If the value might be in either of two tabs, nest one XLOOKUP inside the other’s not-found argument. With the key in A2, the North tab’s IDs in column A and names in column B, and the same layout on South, use:

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

This checks North first. If the key is there, the formula returns the corresponding North value and does not need the second lookup. If it is missing from North, the inner XLOOKUP searches South. For example, if A2 contains 517, North has no 517, and South has 517 paired with “Mina Chen,” the result is Mina Chen. If neither sheet has the key, the result is Not found.

The order matters when the same key occurs on both tabs: North wins because it is searched first. Reverse the two lookups if South should take priority. This approach is useful for checking an archive after a current list, or a regional tab after a central list. It is less suitable for combining every row from both sheets into one consolidated table; that is a different task from finding one match.

Understand the XLOOKUP arguments

The basic Google Sheets syntax is XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode]). The square brackets in function documentation mark optional arguments; do not type the brackets into a formula. For the common cross-sheet lookup, the first three arguments are enough, while the fourth gives you control over the no-match result.

search_key: the value to find, often a cell reference such as A2.
lookup_range: the one-dimensional range to search, such as Inventory!A:A.
result_range: the range containing the answer at the matching position, such as Inventory!B:B.
missing_value: an optional value or message to return if no match exists.
match_mode and search_mode: optional settings for what counts as a match and in which direction to search.

By default, XLOOKUP looks for an exact match and searches from the first item toward the last. That default suits IDs, names, and codes. If you intentionally need a different match rule, specify the relevant optional argument rather than assuming that a near match will behave like an exact one.

Exact, approximate, and wildcard matches

For match_mode, 0 requests an exact match, 1 allows an exact match or the next larger item, -1 allows an exact match or the next smaller item, and 2 enables wildcard matching. The default is exact matching. Approximate modes can be useful for ordered thresholds, such as assigning a band based on a score, but they should not be used casually for product IDs or account codes.

Wildcard mode can help when the search key is a pattern rather than a complete text value. In wildcard patterns, an asterisk represents any sequence of characters and a question mark represents one character; a tilde can escape a wildcard character when you need to search for it literally. Use this mode only when partial pattern matching is intended, because a pattern may match more than one row and the first matching row is normally returned.

The optional search_mode controls the search direction. The default searches from the first item to the last; -1 searches from the last item to the first. A reverse search is useful when a key appears more than once and the latest row is the one you want, provided the rows are arranged in the intended order. The binary search modes require the lookup range to be sorted in the required order. For ordinary unsorted lists, keep the default search mode.

Use the right ranges and handle duplicates

Whole-column references such as A:A are convenient because new rows are included automatically. In a very large or calculation-heavy workbook, bounded ranges such as A2:A5000 may be more efficient and make the intended data area clearer. If you switch to bounded ranges, keep the lookup and result ranges the same height and start them on corresponding rows.

XLOOKUP returns one match, not every row with that key. With duplicate keys and the default search direction, it returns the first match it encounters. Before relying on the result, decide whether duplicates are expected, an error in the source data, or a reason to search from the bottom. A lookup formula cannot determine which duplicate is “correct” without a rule supplied by the workbook’s design.

Also decide what a blank result means. A blank cell in the result range may be a legitimate answer. In the two-tab formula, if the key exists on North but its return cell is blank, XLOOKUP has found a match; it will not then search South. The fallback runs when the lookup key is not found, not whenever the returned value is blank.

Common problems and practical checks

A formula can look right but fail because the values are not truly identical. An ID stored as the number 104 may not match text stored as “104.” Leading or trailing spaces, inconsistent punctuation, and nonprinting characters can also cause a no-match result. Check a few source cells directly and standardize the key data if necessary rather than adding increasingly complicated lookup logic.

No match or #N/A: confirm that the key is present, that the lookup tab and column are correct, and that text and numbers use compatible formats. Add the missing_value argument if you want a readable fallback.
#REF!: check for a misspelled or renamed tab and verify that the referenced range exists.
Wrong result: look for duplicate keys, misaligned lookup and return ranges, or a search direction that does not match your data.
Unexpected partial match: make sure a wildcard match mode has not been selected when you need an exact match.

If a tab name contains spaces, forgetting its single quotes is a common reference problem. Another frequent mistake is selecting the right lookup column but the wrong return column. Test the formula on a key whose expected result you can verify, then check a missing key and any known duplicate before filling the formula down a long list.

XLOOKUP compared with VLOOKUP and alternatives

VLOOKUP is a familiar alternative, but it searches the first column of a table and returns a value a specified number of columns to its right. XLOOKUP names the lookup and result ranges separately, can return a value from either side of the lookup column, and offers a built-in not-found argument. For a straightforward exact lookup in Google Sheets, XLOOKUP is often easier to maintain.

INDEX and MATCH can also perform flexible lookups and may be useful in older spreadsheet environments or in a workbook built around that pattern. For a lookup across two tabs, however, nesting XLOOKUP calls makes the fallback order visible in one formula. If the goal is to append or combine whole datasets from multiple tabs, use a data-combination approach instead of expecting XLOOKUP to return all matching rows.

Google Sheets and Microsoft Excel both provide XLOOKUP, and the central idea is similar, but their broader formula and workbook behavior can differ. A formula written for Google Sheets should be checked if it is moved into another spreadsheet program, especially when it uses newer functions or program-specific features. In Sheets, the examples here use the normal comma-separated formula syntax; some spreadsheet locales use semicolons as argument separators, so the separator may need to match the file’s locale.

Frequently Asked Questions

Can XLOOKUP search another tab in Google Sheets?
Yes. Prefix the lookup and result ranges with the tab name and an exclamation mark, such as Inventory!A:A and Inventory!B:B. Put single quotes around a tab name that contains spaces.

How do I search two sheets and return the first match?
Nest the second XLOOKUP in the first lookup’s missing_value argument. The first tab is checked first, and the second is searched only if the first has no match. Reverse the formula order to give the other tab priority.

Does XLOOKUP in Google Sheets require the lookup column to be first?
No. Specify the lookup range and result range separately, so the result can be in a column to the left or right of the search column. Make sure the ranges line up row by row.

Why does XLOOKUP return the wrong duplicate?
By default, it returns the first match in the lookup range. If you want the last occurrence, use the reverse search option, and check that the row order represents what “last” should mean in your data.

Can XLOOKUP return “Not found” instead of an error?
Yes. Add a message as the fourth argument, such as “Not found”. This handles a key that is absent; it does not fix an invalid tab reference or other formula error.

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.