Does Google Sheets Have XLOOKUP? How to Use It

Yes. Google Sheets has a built-in XLOOKUP function that finds a value in one range and returns the corresponding value from another. You can use it for exact matches, choose how it handles missing results, search from either end of a range, and perform other kinds of matches without the column-order limitations of VLOOKUP.

What XLOOKUP does in Google Sheets

XLOOKUP connects a lookup value to a result. For example, if a sheet contains product codes in one column and prices in another, XLOOKUP can find a code and return its price. The lookup range and result range are specified separately, so the result does not have to be to the right of the values being searched.

The function is part of Google Sheets, not an add-on or a script. In a browser, enter it in a cell like other formulas, or begin typing its name after an equals sign and use Sheets’ function suggestions. It can be useful for inventory lists, staff directories, rosters, order tables, and other data where a value in one column identifies information stored elsewhere.

XLOOKUP returns a corresponding result from a single row or column. Its basic use is a one-to-one lookup: one search key, one matching position, and the result from that position. It is not a database join, and it does not automatically combine every row that shares a key. If several records match, the default behavior is to return the first match it finds.

Arguments and how the syntax works

XLOOKUP takes three required pieces of information: the value to find, the range to search, and the range from which to return a result. It also accepts optional settings for a missing value, match type, and search direction. The lookup and result ranges should have compatible shapes—for example, both can be vertical columns with the same number of rows.

Search key: the value you want to find, often a cell reference.
Lookup range: the row or column in which Sheets should look.
Result range: the corresponding row or column containing the answer.
Missing value: optional text or another value to return when there is no match, rather than an error.
Match mode: optional setting for exact, approximate, or wildcard matching.
Search mode: optional setting for search direction and, in certain cases, binary search.

Exact matching is the default, which makes it a sensible choice for identifiers such as product codes or employee numbers. In a formula, optional arguments can be left out when the default behavior is appropriate. Spreadsheet locales can use different formula separators; if a comma-separated formula is rejected, check the spreadsheet’s locale and use the separator expected by that setting.

A practical example with a product list

Suppose A2:A5 contains product codes, B2:B5 contains product names, and C2:C5 contains prices. Cell E2 contains the code to look up. With that layout, enter this formula to return the matching price and show a message if the code is absent:

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

If the code in E2 is listed in A4, the formula returns the value from C4. If E2 contains a code that is not in A2:A5, it displays “Not found.” The fourth argument is optional, but providing it can make a report easier to read than showing a missing-match error. You can use a cell reference for the search key, or type a text value directly into a formula, provided the text is enclosed in quotation marks.

The same lookup can return a product name instead of a price by pointing the result range at column B. With product codes in A2:A5, names in B2:B5, and the search code in E2, this formula returns the matching name:

=XLOOKUP(E2,A2:A5,B2:B5,"Not found")

Notice that the formula refers directly to the return column. You do not need to count how many columns separate the lookup column from the answer, as you would with a column-index argument in VLOOKUP. That can make formulas easier to maintain if someone inserts another column into the table.

Match types and search direction

For ordinary code, name, or ID lookups, use the default exact match. XLOOKUP also has options for other situations. Approximate matching can find an exact value or the next larger or smaller value, which can help with threshold tables such as grade boundaries or shipping bands. The appropriate direction depends on how the thresholds and rules are defined, so verify the returned boundary with a small test case before applying it to a large sheet.

Wildcard matching is useful when the search text is only part of a value. With product descriptions in A2:A20, the values to return in B2:B20, and the text fragment in E2, this formula looks for a description containing that fragment:

=XLOOKUP("*"&E2&"*",A2:A20,B2:B20,"No match",2)

The final argument shown here sets wildcard matching. In this mode, an asterisk represents any number of characters and a question mark represents one character. Wildcards are not the default: without the wildcard match setting, an asterisk in a search value is normally treated as a literal character. Wildcards can also produce unexpected results if the search fragment occurs in more than one entry, because XLOOKUP returns one match rather than every match.

By default, a search proceeds from the first item toward the last. To return the last matching record instead, with IDs in A2:A100, dates or other results in C2:C100, and the search ID in E2, use:

=XLOOKUP(E2,A2:A100,C2:C100,"Not found",0,-1)

This can be handy when a log contains repeated IDs and the newest entry is at the bottom. The formula searches in reverse and returns the result aligned with the last exact match. Binary search modes are also available, but they depend on data being sorted in the required order; for unsorted everyday lists, the standard search mode is usually less error-prone.

Why choose XLOOKUP over VLOOKUP?

VLOOKUP searches the first column of a table and returns a value from a column to its right. XLOOKUP instead takes separate lookup and result ranges, so it can return information from a column on either side of the lookup range. This avoids rearranging a table just to put the key at the left, and avoids specifying a numbered result column that can become wrong if the table changes.

XLOOKUP also makes it straightforward to provide a custom result for a missing key, and its match and search settings are included in the function. These differences do not mean VLOOKUP is useless: it remains common in existing spreadsheets, and may be easier when a workbook already uses it consistently. If you share formulas between Google Sheets and Excel, test the workbook in both applications rather than assuming every function or behavior is identical in every version.

Common errors and how to fix them

A lookup that appears correct can fail because of range alignment, data types, or subtle differences in the source values. Check these points before rebuilding the formula:

Ranges do not line up: the lookup and result ranges should have corresponding positions and compatible dimensions. If the lookup range has 100 rows but the result range covers a different set of rows, returned values may not represent the intended records.
Numbers are stored as text: a numeric-looking ID stored as text may not match the same digits stored as a number. Make the source values consistent; do not convert identifiers blindly if leading zeros matter.
Extra spaces or different spelling: a trailing space, punctuation change, or spelling difference can prevent an exact match. Check the underlying cell contents, not just how they appear on screen.
Duplicate keys: the normal search returns the first matching entry. If duplicates are possible, decide whether the first or last occurrence is appropriate, or use a method designed to show all matching rows.
Approximate or wildcard mode selected by mistake: review optional arguments if the returned result seems close but not exact, or if a partial match appears unexpectedly.
Wrong locale separator: if Sheets reports a formula parse error, the spreadsheet’s locale may expect semicolons instead of commas.

When diagnosing a formula, test it against one key whose correct answer you can verify manually. Also inspect the exact row returned, especially if the lookup range contains duplicates or near-identical labels. A formula can calculate successfully and still answer the wrong business question if the chosen match rule does not fit the data.

Google Sheets and Excel compatibility

XLOOKUP is available in Google Sheets and in modern Excel, including Microsoft 365. The general idea and familiar arguments make it convenient when moving a simple lookup between the two products, but the applications are separate spreadsheet platforms. A workbook’s behavior can also depend on its file format, locale, and the specific features used in its formulas. If a file is shared with people using older spreadsheet software, check whether their version recognizes XLOOKUP; older Excel versions may not.

In Google Sheets, use the function directly in a cell in the browser; there is no setting to turn it on. If the spreadsheet is opened or exported in another program, confirm that the function remains supported there. For collaboration, it is also helpful to label the lookup and result columns clearly and avoid relying on a formula that only one recipient’s software can calculate.

Alternatives when XLOOKUP is not the right tool

Use VLOOKUP when you need to support older software or maintain an existing sheet built around that function. INDEX and MATCH can also perform flexible lookups and are useful in established workbooks. If the goal is to return every row meeting a condition, rather than a single corresponding value, a filtering approach such as FILTER is usually a better fit. Choose based on the result you need and the spreadsheet applications your collaborators use, not just on which formula is newest.

There is also a difference between looking up one result and summarizing repeated records. For example, if many transactions share the same customer ID and you need their total, a lookup is not a substitute for a conditional sum or a pivot table. XLOOKUP selects a corresponding value; it does not add together every matching transaction. That distinction prevents a common mistake when people first apply lookup formulas to data with repeated keys.

Frequently Asked Questions

Is XLOOKUP free in Google Sheets?
Yes. It is a built-in Google Sheets function and does not require a paid add-on or separate installation. You need access to a Google spreadsheet where you can enter formulas.

Does XLOOKUP in Google Sheets default to an exact match?
Yes. Exact matching is the default. You can still provide optional arguments to choose an approximate or wildcard match when the data calls for it.

Can XLOOKUP return a value to the left of the lookup column?
Yes. The result range is specified independently, so it can be positioned to the left or right of the lookup range. Make sure the two ranges line up row by row or column by column.

How do I make XLOOKUP return the last match?
Set its search mode to negative one for a reverse search. The formula example above uses this option to search from the end of a vertical list toward the beginning.

Why does XLOOKUP say a value is missing when it is visible in the sheet?
Check for text-versus-number differences, extra spaces, punctuation, and mismatched ranges. A displayed value can look identical while its stored contents differ, particularly with IDs that include leading zeros.

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.