XLOOKUP in Google Sheets: Syntax, Examples, and Tips

To look up a value in Google Sheets, use =XLOOKUP(search_key, lookup_range, result_range, "Not found"). For example, if product IDs are in A2:A6, prices are in B2:B6, and the ID to find is in E2, use =XLOOKUP(E2,A2:A6,B2:B6,"Not found") to return its price or a clear message when there is no match.

What XLOOKUP does

XLOOKUP searches one row or one column for a value, then returns the corresponding value from another row or column. It is useful for matching an order number to a status, a product code to a price, or an employee ID to a name. Unlike a simple lookup that assumes the return data is to the right of the search data, XLOOKUP lets you choose the result range independently.

The function is available in Google Sheets and works in the browser. It does not require an add-on or a special setting. A formula recalculates when its referenced cells change, so a lookup can keep a report current as a table is updated. XLOOKUP is a function name, not a menu command: enter it in a cell or in the formula bar, just as you would enter SUM.

For example, a table can place prices to the left of product codes and still be searched with XLOOKUP. That flexibility is useful when a worksheet’s layout cannot easily be rearranged. It also makes formulas easier to read because the search range and the returned information are stated separately.

Syntax and arguments

The full Google Sheets syntax is XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode]). The first three arguments are the essentials; the arguments in square brackets are optional. In formulas below, commas separate arguments. Depending on a spreadsheet’s regional settings, Sheets may display or require semicolons instead.

search_key is the value to find, often a cell reference such as E2.
lookup_range is the single row or column to search.
result_range is the corresponding row or column from which to return a result. Its shape should match the lookup range.
missing_value is what to return if there is no match. If omitted, XLOOKUP returns an error for a missing match.
match_mode controls exact, approximate, or wildcard matching.
search_mode controls the direction or method used to search.

Keep the lookup and result ranges aligned. If A2:A20 contains IDs, B2:B20 should contain the corresponding names; using B3:B21 by mistake can return a plausible but incorrect result. Bounded ranges also make formulas easier to inspect than references to whole columns, particularly in large or complex sheets.

A basic exact-match example

Suppose A2:A6 contains the product IDs P-104, P-105, P-106, P-107, and P-108, while B2:B6 contains their prices: 8.99, 12.50, 4.75, 19.00, and 6.25. Put P-106 in E2. With IDs in A2:A6 and prices in B2:B6, enter:

=XLOOKUP(E2,A2:A6,B2:B6,"Not found")

The result is 4.75, because P-106 is the third value in the lookup range and 4.75 is the third value in the result range. XLOOKUP uses exact matching by default, so this formula does not require a separate instruction to match the ID precisely. Including “Not found” provides a readable response if E2 contains an ID that is not in A2:A6.

To create the formula, select the result cell, type an equals sign and the function, and supply the arguments in order. You can type range references or select the cells with the mouse. Press Enter to commit the formula. If you copy it down a list of search keys, relative ranges such as A2:A6 may shift; use absolute references such as $A$2:$A$6 when the lookup table should stay fixed.

Match modes and approximate lookups

For ordinary text labels, IDs, and exact codes, the default match mode is usually right. When you need a different kind of match, the fifth argument changes the behavior: 0 means exact match, -1 means exact match or the next smaller item, 1 means exact match or the next larger item, and 2 enables wildcard matching.

Approximate matching can help with ordered bands such as tax brackets, shipping thresholds, or grading cutoffs, but the lookup data needs to be arranged with the chosen direction in mind. For instance, assume A2:A5 contains ascending minimum scores 0, 60, 70, and 80, and B2:B5 contains the corresponding grades F, D, C, and B. If E2 contains 74, use this formula to find an exact score or the next smaller threshold:

=XLOOKUP(E2,A2:A5,B2:B5,"No band",-1)

The result is C, because 70 is the largest threshold no greater than 74. If you use approximate modes with unsorted or incorrectly ordered values, the result may not represent the intended band. Check the order and boundary rules carefully, especially where the value lies between two thresholds.

Wildcard matching is useful when the search text is only part of a label. Suppose A2:A10 contains product descriptions and E2 contains the word “mug.” With descriptions in A2:A10 and categories in B2:B10, this formula returns the category associated with the first description containing that text:

=XLOOKUP("*"&E2&"*",A2:A10,B2:B10,"No match",2)

In wildcard patterns, an asterisk represents any sequence of characters and a question mark represents one character. A tilde can be used to treat a wildcard character literally. Wildcard searches are not the same as approximate numeric matching; choose match mode 2 specifically when you want wildcard behavior.

Choosing search direction and finding the latest match

By default, XLOOKUP searches from the first item to the last and returns the first match it encounters. If an ID appears more than once and you want the most recent or last-listed entry, set the sixth argument, search_mode, to -1. This searches from the last item toward the first.

For example, assume transaction IDs are in A2:A100, dates are in B2:B100, and statuses are in C2:C100. If E2 contains a transaction ID and you want the status from its last occurrence, use:

=XLOOKUP(E2,A2:A100,C2:C100,"No match",0,-1)

The result is the status in column C on the last row where that ID occurs. This does not automatically mean “latest” in a chronological sense: it means last in the range’s current order. If rows are not sorted by date, the last occurrence may not be the newest date. The other search modes, 2 and -2, use binary searches for ascending or descending data. Use them only when the lookup range is sorted as required; a misordered range can produce an incorrect answer.

Common problems and how to fix them

A lookup that seems correct can still return an error or the wrong result. Check the following points before rebuilding the formula:

Ranges do not line up: Make sure the lookup and result ranges contain corresponding items and have compatible dimensions.
Unexpected missing match: A number stored as text may not match the same-looking numeric value. Check the source cells’ values and formatting; formatting alone does not always change the underlying data type.
Extra spaces or inconsistent characters: Imported data may contain leading spaces, nonbreaking spaces, or different punctuation. Clean or standardize the source values if they should be equivalent.
Duplicate keys: The default search returns the first match, not every matching row. Use reverse search to return the last match, or use another approach if you need all matches.
Wrong approximate result: Verify the match mode and sorting order. An approximate lookup is not a substitute for exact matching when IDs or codes must be identical.
Formula parse error: Check commas versus semicolons for your locale, quotation marks, and parentheses. Use straight quotation marks in formulas.

To make errors easier to diagnose, test the formula on a small example with a known result. Also confirm that the search key is in the intended cell and that the formula points to the intended sheet and ranges. When referring to a different tab, use the sheet name in the reference, for example =XLOOKUP(E2,Inventory!A2:A100,Inventory!B2:B100,"Not found"). If a sheet name contains spaces, enclose it in single quotes, as in 'Current Stock'!A2:A100.

XLOOKUP compared with VLOOKUP and INDEX/MATCH

VLOOKUP searches the first column of a table and returns a value from a numbered column to its right. That structure can be awkward if the result is to the left of the lookup column, or if inserting a column changes the column number in a formula. XLOOKUP names the lookup and result ranges separately, so it can search in either direction and does not use a column index number.

INDEX and MATCH can also search flexibly and remain useful in older spreadsheets or workbooks designed around those functions. They require a combination of functions, whereas XLOOKUP puts the search, return range, and optional missing-value response in one expression. The best choice depends on the sheet’s existing formulas, compatibility needs, and the people who will maintain it.

Excel also has an XLOOKUP function, but this page describes Google Sheets behavior and its browser interface. Formula syntax and available options are similar for common use, but do not assume every spreadsheet application handles every feature identically. If you share a file with Excel users, verify the function and formula behavior in the target application.

Frequently Asked Questions

Is XLOOKUP available in Google Sheets?
Yes. XLOOKUP is a built-in Google Sheets function. Enter it in a cell like other formulas; you do not need to install an extension.

Does XLOOKUP in Google Sheets default to an exact match?
Yes. The default match mode is exact. For IDs and other values that must match precisely, you can normally omit the match-mode argument.

How do I make XLOOKUP return something other than an error when no match exists?
Supply a missing-value argument, such as "Not found", as the fourth argument. For example, =XLOOKUP(E2,A2:A20,B2:B20,"Not found") returns that text when E2 is absent from A2:A20.

Can XLOOKUP return a value from a column to the left?
Yes. The result range is specified separately from the lookup range, so it can be located to the left or right, provided the ranges correspond in size and order.

How do I return the last match instead of the first?
Set the search mode to -1, the sixth argument. If you also specify a missing value and match mode, include those arguments before it; for example, =XLOOKUP(E2,A2:A100,C2:C100,"No match",0,-1).

Why does XLOOKUP say a value is missing when it looks identical?
The cells may differ in underlying type, contain hidden spaces, or use different characters. Compare the source values and clean or standardize them before relying on the lookup.

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.