XLOOKUP in Google Sheets: Formula, Examples & Tips

Google Sheets has an XLOOKUP function for finding a value in one range and returning the corresponding value from another. For example, with product IDs in A2:A6, product names in B2:B6, and the ID to find in D2, use =XLOOKUP(D2,A2:A6,B2:B6,"Not found").

What XLOOKUP Does in Google Sheets

XLOOKUP searches a row or column for a key, then returns a value from the matching position in a separate result range. That makes it useful for retrieving a product name from an ID, finding a price from a parts list, or returning a department from an employee number. It is available as a native Google Sheets function; you do not need an add-on or an Excel file.

Unlike a lookup formula that relies on a fixed column number, XLOOKUP receives the search range and return range as separate arguments. You can therefore look to the left or right of the search column without rearranging the table. The function also has built-in options for missing keys, approximate matching, wildcard matching, and searching from the bottom of a range.

A lookup only returns information that exists in the ranges you give it. It does not automatically clean inconsistent IDs, decide which duplicate record is correct, or infer a match when text differs. Reliable results depend on choosing aligned ranges and understanding the match rule.

XLOOKUP Syntax and Arguments

The Google Sheets syntax is XLOOKUP(search_key, lookup_range, result_range, missing_value, match_mode, search_mode). The first three arguments are required; the remaining arguments are optional. In a formula, the function name is preceded by an equals sign, as in the opening example.

search_key: The value to find, such as an ID in D2.
lookup_range: The single row or column where Sheets searches for that key.
result_range: The corresponding row or column from which to return a result. It should line up positionally with the lookup range.
missing_value: What to return if there is no match. If omitted and no match exists, XLOOKUP returns an #N/A error.
match_mode: How to decide whether a value matches. The default, 0, requests an exact match.
search_mode: Which direction or search method to use. The default, 1, searches from the first item to the last.

For example, if the lookup range is A2:A20, the result range must correspond to those same records in the same order. If you sort one range independently, or start it on a different row, a match can return the wrong record even though the formula itself is valid.

A Basic XLOOKUP Example

Suppose A2:A6 contains product IDs P-101 through P-105 and B2:B6 contains their product names in the same row order. If D2 contains P-104, this formula searches the IDs and returns the name from the matching row:

With product IDs in A2:A6, names in B2:B6, and the requested ID in D2, use =XLOOKUP(D2,A2:A6,B2:B6,"Not found"). If P-104 is paired with Desk lamp in B5, the result is Desk lamp.

The fourth argument, "Not found", provides a readable result for an ID that is absent. You could use a different message, or omit that argument if you prefer to see #N/A when nothing matches. A custom message can make a report easier to read, but it can also hide the fact that a key is misspelled, so consider whether an error would be more useful while checking data.

To return a value from a column to the left, simply make that column the result range. With names in A2:A6, IDs in B2:B6, and the requested ID in D2, use =XLOOKUP(D2,B2:B6,A2:A6,"No match"). If D2 is P-104 and A5 contains Morgan Lee, the result is Morgan Lee. No table rearrangement or negative column index is needed.

Exact, Approximate, and Wildcard Matches

For ordinary ID, name, or email lookups, the default exact-match behavior is usually the right choice. Google Sheets XLOOKUP uses match mode 0 by default. The other match modes are 1 for an exact match or the next greater item, -1 for an exact match or the next smaller item, and 2 for a wildcard match.

Approximate matching is useful for banded values such as grades, shipping tiers, or commission thresholds, provided the threshold logic is deliberate. For a score of 84, a table with minimum scores 0, 60, 70, 80, and 90 in A2:A6 and labels F, D, C, B, and A in B2:B6 can return the nearest lower threshold’s label:

With minimum score thresholds sorted upward in A2:A6, their grade labels in B2:B6, and a score of 84 in D2, use =XLOOKUP(D2,A2:A6,B2:B6,"No grade",-1). The result is B, because 80 is the largest threshold not greater than 84. Check the sorted thresholds and boundary cases before using approximate matches in a real grading or pricing table.

Wildcard mode supports pattern matching rather than an exact full-cell match. For example, an asterisk can stand for any number of characters. With customer names in A2:A20 and a search fragment in D2, this searches for a name containing that fragment:

With names in A2:A20 and the text to find in D2, use =XLOOKUP("*"&D2&"*",A2:A20,A2:A20,"No match",2). If D2 contains “Lee” and the first matching name is “Morgan Lee,” the result is Morgan Lee. Wildcards are helpful for partial text, but can match more than one record; the default search direction returns the first match it encounters.

Search Direction, Duplicates, and Multiple Results

Search mode 1 searches from the first item to the last and is the default. Search mode -1 searches from the last item to the first. This is useful when a key appears more than once and the last occurrence represents the current or most recent record. It does not remove duplicates; it only controls which matching position is selected.

With transaction IDs in A2:A100, statuses in B2:B100, and the ID to check in D2, use =XLOOKUP(D2,A2:A100,B2:B100,"No transaction",0,-1) to return the status from the last matching row. If ID T-22 appears in rows 8 and 40 and the later row’s status is Closed, the result is Closed. This is only a sound shortcut if row order really represents the rule you want, such as newest entry last.

Search modes 2 and -2 use binary search and require the lookup range to be sorted in the appropriate order. They can be useful for large, sorted data, but an incorrectly sorted range can produce an incorrect result. For most everyday sheets, the ordinary first-to-last or last-to-first search is simpler and less error-prone.

XLOOKUP can also return an array when the result range contains multiple corresponding values or when a range of search keys is used, depending on the formula’s ranges and surrounding sheet space. In Sheets, array results need room to expand into neighboring cells; existing content can prevent expansion. For a simple single-result lookup, keep the lookup range and result range to matching rows or columns and avoid adding extra dimensions unless you specifically want an array output.

How XLOOKUP Compares with VLOOKUP and INDEX/MATCH

VLOOKUP is familiar and still useful, but it searches the first column of a specified table and returns a value from a column to its right, identified by a column number. Inserting or rearranging columns can make that number fragile. XLOOKUP names the search and return ranges separately, can return a value from either side, and has an explicit missing-value argument.

INDEX/MATCH is another flexible approach and remains useful in older spreadsheets or when maintaining a workbook built around those functions. XLOOKUP often expresses a one-dimensional lookup more directly, but it is not a reason to replace every established formula. When collaborating, consider whether other users need to understand or maintain the sheet, and whether an existing template expects a particular formula pattern.

Google Sheets and Excel both offer XLOOKUP, but they are different applications and can differ in function rollout, locale settings, and array behavior. In Sheets, use the Google Sheets function syntax and help documentation rather than assuming every Excel feature behaves identically. Formulas here use commas between arguments; some spreadsheet locales use semicolons as separators, so a formula may need that punctuation adjusted in a locale configured that way.

Common Errors and Ways to Troubleshoot

#N/A usually means there was no match under the selected match mode. Check for leading or trailing spaces, numbers stored as text, inconsistent punctuation, and a key that is simply absent. A visible value such as 0012 may not behave like numeric 12, and similar-looking characters can differ. If you use a custom missing-value message, temporarily remove it while diagnosing the lookup so a failed match is not mistaken for a valid result.

A result from the wrong record often points to duplicate keys, a misaligned result range, or an unintended approximate or wildcard mode. Confirm that both ranges cover the same records in the same order. If duplicates are expected, decide whether the first or last match is the correct business rule and set search mode accordingly.

Also verify that the lookup range is a single row or column and that the result range corresponds to it. If you choose binary search mode, sort the lookup values in the required direction first. If a formula is intended to return multiple cells, check that the cells where its results need to appear are empty; an occupied cell can block an array expansion.

Check that the formula references the intended sheet and rows.
Compare the key and lookup values for data type, spacing, and spelling differences.
Confirm that approximate matching is intentional and that threshold values are ordered correctly.
Test a known key and a key that should not exist before copying the formula down.

When to Use XLOOKUP—and When Not To

Choose XLOOKUP when one key should identify a corresponding value, you need a leftward or rightward lookup, or you want a readable fallback instead of an unhandled missing-key error. It is especially convenient in a maintained list where rows may be added and columns may move, because the formula refers directly to ranges rather than a hard-coded return-column number.

Use another approach when the task is not really a one-key lookup. FILTER is often a better fit when you want every row that meets a condition rather than only one match. A pivot table may be more suitable for summaries, and a database-style arrangement may be needed when relationships span many tables. For a quick search in a small sheet, Find can be enough; no lookup formula is necessary if you only need to locate a cell yourself.

Keep keys consistent and unique when possible. If a customer has multiple orders, looking up a customer name alone cannot identify which order you mean; use a unique order ID or define a rule such as “most recent row.” XLOOKUP selects a position according to its settings—it does not know the meaning of your records.

Frequently Asked Questions

Does XLOOKUP work in Google Sheets?
Yes. XLOOKUP is a built-in Google Sheets function. Enter it in a cell like other formulas; no Excel license or add-on is required.

What is the basic XLOOKUP formula in Google Sheets?
Use =XLOOKUP(search_key,lookup_range,result_range,missing_value). For example, =XLOOKUP(D2,A2:A20,B2:B20,"Not found") searches for D2 in A2:A20 and returns the aligned value from B2:B20, or the message if there is no match.

Can XLOOKUP look to the left?
Yes. Put the range containing the key in the lookup_range argument and the range containing the desired result in result_range. The result range can be on the left or right, as long as its entries align with the lookup range.

How do I return the last matching result?
Set the sixth argument, search_mode, to -1. For example, =XLOOKUP(D2,A2:A100,B2:B100,"No match",0,-1) searches from the bottom upward and returns the result paired with the last matching key.

Why does XLOOKUP return #N/A?
It did not find a match under the formula’s match mode. Check the key, range references, extra spaces, and whether numbers or dates are stored in compatible formats. You can provide a missing_value argument for a friendlier message, but first make sure an absent match is not evidence of a data problem.

Is XLOOKUP better than VLOOKUP in Sheets?
For many direct lookups, XLOOKUP is more flexible because it can return values from either side and does not require a return-column number. VLOOKUP remains usable, especially in existing sheets, so the better choice depends on compatibility and how the spreadsheet is maintained.

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.