XLOOKUP in Google Sheets: How It Works and When to Use It

Yes. Google Sheets supports XLOOKUP, so you can search one range for a value and return the corresponding value from another range. For example, =XLOOKUP(E2,A2:A6,B2:B6,"No match") looks for the value in E2 in A2:A6 and returns the corresponding entry from B2:B6, or “No match” if it is absent.

XLOOKUP availability in Google Sheets

XLOOKUP is a built-in Google Sheets function and works in Sheets in a web browser; it is not an Excel-only feature. You do not need to install an add-on or write a script to use it. As with other spreadsheet functions, the exact options available in your account may depend on Google’s product updates, but XLOOKUP is part of the standard Sheets function set.

The function is useful whenever one piece of information identifies another. A product code can retrieve a price, an employee ID can retrieve a department, or a date can retrieve a matching status. XLOOKUP keeps the search range and the return range separate, which makes the formula easier to understand than formulas that rely on a column number.

Google Sheets and Excel use the same function name and a broadly similar argument structure, but they are separate applications. If you move a spreadsheet between them, check that the destination application supports XLOOKUP and confirm that the formula still returns the intended result. Excel support depends on the version; older Excel releases may not recognize the function.

How the XLOOKUP syntax works

The general Google Sheets syntax is XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode]). The brackets indicate optional arguments; do not type the brackets into a formula. The first three arguments are the essentials:

search_key: The value you want to find, such as a cell reference or a text or numeric value.
lookup_range: The row or column where Sheets should search for that value.
result_range: The corresponding row or column from which Sheets should return a result.

The lookup and result ranges need to line up: a match at a given position in the lookup range returns the value at the corresponding position in the result range. For ordinary lookups, use single-column ranges or single-row ranges of the same length. A vertical search range pairs naturally with a vertical return range; a horizontal search range pairs with a horizontal one.

The fourth argument, missing_value, lets you supply a result for a value that is not found. If you leave it out, an unsuccessful exact search returns #N/A. The fifth and sixth arguments control matching and search direction, which can be useful but are not needed for a basic exact lookup.

A basic lookup with a concrete example

Suppose A2:A6 contains product IDs, B2:B6 contains product names, and C2:C6 contains prices. E2 contains the ID you want to look up. With IDs in A2:A6, prices in C2:C6, and the requested ID in E2, enter:

=XLOOKUP(E2,A2:A6,C2:C6,"No match")

If E2 contains an ID whose price is 18.50, the formula returns 18.50. If E2 contains an ID not listed in A2:A6, it returns the text “No match” instead of an error. To return a product name rather than a price, keep the same lookup range but use B2:B6 as the result range.

Using a cell reference such as E2 means you can change the search key without editing the formula. You can also fill the formula down a column for multiple keys. If the ranges should remain fixed while copying, use absolute references. With the same layout, an absolute-range version is:

=XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6,"No match")

Here the dollar signs keep the product and price ranges in place as the formula is copied, while E2 can change to E3, E4, and so on. If you copy formulas horizontally as well, consider whether the search-key reference should also be fixed.

Exact matches, approximate matches, and search direction

By default, XLOOKUP looks for an exact match. That is usually the safest choice for identifiers such as product codes, email addresses, and employee numbers. The optional match_mode argument changes the kind of match:

0 means exact match and is the default.
1 means exact match or the next larger value.
-1 means exact match or the next smaller value.
2 enables wildcard matching, including patterns using * and ?.

Approximate matching is useful for ordered categories such as rate bands or score thresholds. For example, suppose A2:A5 holds ascending minimum order amounts and B2:B5 holds the corresponding shipping charge. With order amount in E2, thresholds in A2:A5, and charges in B2:B5, this formula finds an exact threshold or the closest smaller one:

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

For a threshold lookup, make the bands clear and verify the results around each boundary. Approximate matching is not a substitute for checking the business rule: an amount below the smallest threshold may not have a suitable smaller tier to return.

The optional search_mode controls the direction or method of searching. Its default, 1, searches from first to last. Use -1 to search from last to first; this can return the most recent matching row when a key appears more than once. With IDs in A2:A100, corresponding dates in B2:B100, and the requested ID in E2, this returns the date from the last matching row:

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

Search modes 2 and -2 use binary search on data sorted in ascending or descending order, respectively. Use them only when the lookup range is sorted in the required order. If it is not, a binary search can produce an incorrect result rather than a helpful warning.

Wildcard searches and repeated matches

Wildcard mode can find text containing a fragment, but the pattern must be used with match mode 2. For example, if A2:A20 contains descriptions, B2:B20 contains category names, and E2 contains a word or phrase to find, use:

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

The asterisks mean “any characters before or after this text.” XLOOKUP returns the first matching result in the search direction, not every row that contains the fragment. A question mark is another wildcard, representing one character. If a search term itself includes wildcard characters and you mean them literally, wildcard rules can make the result surprising; simplify or escape the pattern as appropriate.

When a lookup key occurs multiple times, the default search direction returns the first match from the top. That may be right for a product master list, but not for a log where you need the latest entry. In that case, reverse the search direction as shown above, and ensure the rows are ordered so that the last occurrence really is the record you want. Duplicate keys are not automatically treated as errors.

XLOOKUP compared with VLOOKUP and alternatives

Compared with VLOOKUP, XLOOKUP does not require the lookup column to be the leftmost column in a table. You explicitly identify both the search range and the return range, so inserting another column between them is less likely to break the formula through a changed column index. XLOOKUP also defaults to an exact match, while VLOOKUP can use approximate matching if its final argument is omitted. That difference is a common source of unexpected VLOOKUP results.

VLOOKUP remains familiar and may be useful when maintaining an older spreadsheet or sharing a file with a system that expects that function. INDEX and MATCH are another established combination, particularly in workbooks designed for compatibility with older spreadsheet versions. In a Google Sheets-only file, XLOOKUP is often more readable for a straightforward one-result lookup, but it is not automatically the best tool for every task.

If you need every matching row rather than just the first result, use FILTER instead. With IDs in A2:A20, associated values in B2:B20, and the requested ID in E2, the following returns all values in B whose IDs equal E2:

=FILTER(B2:B20,A2:A20=E2)

FILTER can return multiple rows, so leave space below the formula for its results to expand. If no rows meet the condition, FILTER returns an error unless you provide an optional value to use when nothing matches. XLOOKUP is better suited to a single corresponding result; FILTER is often clearer when duplicates are meaningful and you want to see them all.

Common XLOOKUP problems and how to fix them

#N/A: The search key may not exist in the lookup range, or the formula may be searching the wrong range. Add a missing-value argument if a friendly fallback is appropriate, then verify the key and range.
Unexpected non-match: A value that looks identical may contain extra spaces, different punctuation, or a different underlying type. Check whether one value is stored as text and the other as a number; formatting a cell to look numeric does not necessarily convert its stored value.
Wrong duplicate returned: XLOOKUP returns one match. Confirm whether you want the first occurrence or the last, and choose the search direction accordingly.
Unexpected approximate result: Inspect the match mode. For IDs and other exact identifiers, leave the default exact mode or explicitly use 0. If using binary search, confirm that the lookup data is correctly sorted.
Formula parse error: Check commas, parentheses, and quotation marks. Google Sheets uses spreadsheet locale settings, and some locales use semicolons between function arguments instead of commas.

When investigating a result, test the lookup key in a nearby cell and inspect the source range directly. Also make sure lookup and result ranges have matching orientation and corresponding positions. A formula can be syntactically valid yet return a misleading value if the ranges refer to the wrong rows.

Using XLOOKUP in shared spreadsheets

For a shared Google Sheet, make the input and source ranges easy for other editors to identify. A label beside the search cell, a clear header row, and a readable missing-value message can prevent someone from mistaking a blank or error for a real result. Avoid including headers in a lookup range unless they are intentionally part of the search data.

When you copy a formula down, check both relative and absolute references. A common pattern is to keep the source table fixed with dollar signs and allow the input-cell reference to move by row. If you later sort the source data, XLOOKUP’s normal first-to-last search follows the new row order; a “first” result is not necessarily the oldest or newest record unless the table is ordered accordingly.

If people will download the sheet as an Excel file, explain that the formula relies on XLOOKUP and consider the versions they use. Microsoft 365 and newer perpetual Excel releases support it, while older Excel versions may show an unrecognized-function error. For a one-off export, check the downloaded file rather than assuming the formula will behave identically in every spreadsheet program.

Frequently Asked Questions

Is XLOOKUP free in Google Sheets?
It is a built-in Sheets function, not a separate paid add-on. You can use it in a Google Sheets spreadsheet without installing an extension.

Does Google Sheets XLOOKUP return the first or last match?
By default, it searches from the first item to the last and returns the first match. Set the search mode to -1 to search backward and return the last match.

Can XLOOKUP return a value to the left of the search column?
Yes. The lookup range and result range are specified separately, so the result range can be to the left or right of the lookup range. Make sure the ranges line up position by position.

Why does XLOOKUP show #N/A?
Usually, the key was not found as an exact match, or the formula references the wrong range. Check for extra spaces, text-versus-number differences, and the lookup value itself; you can also supply a missing-value result.

Can XLOOKUP return multiple matching rows?
No—not as a list of every row matching the key. It returns a corresponding result for a match. Use FILTER when you want to return all rows that meet a condition.

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.