XLOOKUP in Google Sheets: How It Works

Yes—Google Sheets has a built-in XLOOKUP function, so you can search a row or column and return a corresponding value without an add-on. Its basic syntax is =XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode]); an exact match is the default.

What XLOOKUP Does in Google Sheets

XLOOKUP finds a value in one range and returns the value at the corresponding position in another range. For example, you can search a product-code column and return a price from a separate column. The lookup range and result range must line up: a match in the first range determines which position to retrieve from the second.

Unlike older lookup approaches that impose a particular column order, XLOOKUP lets you specify the search range and return range separately. The return range can be to the left or right of the search range. This makes it useful when a spreadsheet’s columns are arranged for people to read rather than for a lookup formula to work.

It is a normal spreadsheet formula, not a special add-on or script. Enter it in a cell in Google Sheets in a web browser, then edit its arguments to fit your data. If your Google Sheets locale uses semicolons as formula separators, replace the commas shown in these examples with semicolons.

How the XLOOKUP Arguments Work

The first three arguments provide the essential lookup: the value to find, the range in which to search, and the range from which to return a result. The ranges should have matching dimensions—for example, one column of search keys paired with one column of results, or one row paired with another row. The lookup range itself must be a single row or a single column.

search_key is the value to find, often a cell reference such as E2.
lookup_range is the row or column to search.
result_range is the corresponding row or column containing the answer.
missing_value is optional text or a value to display if there is no match.
match_mode and search_mode are optional settings for match type and search direction.

With product codes in A2:A5, product names in B2:B5, prices in C2:C5, and the code B-204 in E2, use this formula to return the price:

=XLOOKUP(E2,A2:A5,C2:C5,"Not found")

If B-204 is on the third data row, the result is the price in C4. If the key is absent, the formula displays Not found instead of an error. You can omit the fourth argument, but then an unsuccessful search returns an error rather than a custom message.

Exact Matches, Missing Values, and Search Direction

For ordinary lookups, XLOOKUP’s default exact-match behavior is usually what you want. It searches for a value that matches the search key rather than choosing a nearby number. That is a useful default for identifiers such as order numbers, email addresses, inventory codes, and names. You do not have to add a separate exact-match setting for these common cases.

By default, the search starts at the first item in the lookup range and moves toward the end. If repeated keys are present, that means the first matching entry is returned. To return the most recently listed match instead, set the search mode to -1. With dates in A2:A100, corresponding statuses in B2:B100, and a date to find in E2, this searches from the bottom upward:

=XLOOKUP(E2,A2:A100,B2:B100,"No match",0,-1)

The fifth argument, 0, explicitly requests an exact match; the sixth, -1, reverses the search direction. If the same date appears more than once, the result comes from its last occurrence in the range. This is useful for logs where later rows represent more recent events, provided the rows are in the expected order.

Approximate and Wildcard Matching

XLOOKUP can also handle cases where an exact key is not appropriate. Its match modes include exact match (0), exact match or the next larger item (1), exact match or the next smaller item (-1), and wildcard matching (2). Approximate matching can be handy for a tier table, such as shipping charges that change at weight thresholds. For a next-smaller match, arrange numeric thresholds in ascending order and check that the boundaries reflect the rule you intend to apply.

For instance, suppose A2:A5 contains ascending minimum-score thresholds and B2:B5 contains the corresponding grade labels. With a score in E2, this requests an exact match or the next smaller threshold:

=XLOOKUP(E2,A2:A5,B2:B5,"Below minimum",-1)

Wildcard mode is different from approximate numeric matching. It allows wildcard characters in the search key: an asterisk can stand for any number of characters, and a question mark can stand for one character. With names in A2:A100, departments in B2:B100, and a fragment in E2, this searches for the first name containing that fragment:

=XLOOKUP("*"&E2&"*",A2:A100,B2:B100,"No match",2)

Use wildcard searches deliberately. A broad fragment may match more than one entry, and the default search direction returns the first match it encounters. Wildcards are also not the right choice when you need a strict identifier match.

XLOOKUP Compared with VLOOKUP and INDEX/MATCH

VLOOKUP is familiar to many spreadsheet users, but it searches the first column of a specified table and returns a value from a column to its right. If the answer is to the left of the search column, you generally need to rearrange the data or use another formula. XLOOKUP specifies the lookup and result ranges independently, so it can return a value from either side.

Another difference is that VLOOKUP commonly identifies the return column by a number counted from the start of its table range. Inserting or rearranging columns can make that number point somewhere unexpected. XLOOKUP directly names the return range, which is easier to read and maintain. It also provides a built-in argument for a custom not-found result.

INDEX and MATCH remain useful and widely recognized. For names in A2:A100, sales figures in B2:B100, and a name to find in E2, this returns the matching sales value:

=INDEX(B2:B100,MATCH(E2,A2:A100,0))

The 0 in MATCH requests an exact match. This combination can search in either direction because the lookup and return ranges are separate, much like XLOOKUP. If a spreadsheet already uses INDEX/MATCH reliably, there may be no need to replace it; XLOOKUP is often a more compact option for a new formula.

Common XLOOKUP Problems and How to Fix Them

A formula can be syntactically valid and still return an unexpected result. Check the data and the relationship between the ranges, not just the function name. These are common causes of trouble:

No exact match: A key may contain an extra space, different punctuation, or a value stored as text while the lookup column stores a number. Compare the underlying values and clean inconsistent data where appropriate.
Misaligned ranges: The lookup and result ranges should cover corresponding entries in the same order. If one starts on row 2 and the other starts on row 3, the returned value can belong to a different record.
Duplicate keys: The default search returns the first match. Use a reverse search when the last occurrence is intended, or make the key unique if each record must be individually identified.
Unexpected approximate result: Confirm the match mode. For threshold tables, sort the lookup values as required and verify whether the rule should use the next smaller or next larger threshold.
Not-found message appears: Check for spelling differences and hidden spaces, and confirm that the searched value is actually in the chosen lookup range. A custom missing-value message helps distinguish a no-match outcome from some other formula problem.
Formula parse error: Check the number and order of arguments and whether your locale expects semicolons rather than commas.

One important distinction: the optional missing-value argument handles a search key that is not found. It does not automatically explain every issue in the source data. For example, if a matching row’s result cell is blank, XLOOKUP can return a blank result; that is different from failing to find the key.

When to Use XLOOKUP—and When Not To

XLOOKUP is a good fit when one value identifies a row or position and you want a corresponding value from a parallel range. Common uses include retrieving a product price by SKU, looking up a staff member’s department by employee ID, or finding a customer detail by account number. It is especially convenient when the result column is not immediately to the right of the search column.

It is not always the right tool. If you need every row that meets a condition rather than one corresponding result, consider the Google Sheets FILTER function. If you need to summarize many records by category, a pivot table or a function such as SUMIF may be more suitable. If the key is duplicated but you need a particular record, define what makes that record unique instead of relying on whichever match happens to appear first.

For routine exact lookups, keep the ranges focused on the relevant data and use clear headers. A formula that searches a whole column can be convenient, but a bounded range can make the intended data easier to audit. If the data grows regularly, choose a range that allows for new records without accidentally including unrelated material.

Frequently Asked Questions

Is XLOOKUP available in Google Sheets?
Yes. XLOOKUP is built into Google Sheets and can be entered directly as a formula in a browser-based spreadsheet. You do not need an add-on or Apps Script for a standard lookup.

Does Google Sheets XLOOKUP use exact match by default?
Yes. Its default match mode is exact match, which is appropriate for most lookups based on IDs, codes, or names. You can specify another match mode when you need approximate or wildcard matching.

Can XLOOKUP return a value to the left of the search column?
Yes. The lookup range and result range are separate arguments, so the result range can be on either side of the lookup range. Make sure the two ranges correspond to the same rows or columns.

How do I make XLOOKUP show text when it cannot find a value?
Use the optional fourth argument for the message you want displayed. For example, include "Not found" after the result range. If you leave that argument out, a missing key produces an error.

What is the difference between XLOOKUP and VLOOKUP in Sheets?
VLOOKUP searches the first column of its table and returns from a column to the right, commonly selected by a column number. XLOOKUP uses separate lookup and result ranges, can return from either direction, and has an optional not-found value.

Does XLOOKUP in Google Sheets work the same as Excel XLOOKUP?
The core purpose and familiar argument pattern are similar, but Google Sheets and Excel are separate products, and feature details can differ. A formula designed for one platform should be checked in the other rather than assuming every behavior or compatibility detail is identical.

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.