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

Yes. XLOOKUP is a built-in Google Sheets function that searches a row or column for a value and returns a related value from another row or column. It can perform exact and approximate matches, search in reverse, and display a custom result when there is no match.

What XLOOKUP Does in Google Sheets

XLOOKUP connects two ranges: one containing the values to search and another containing the results to return. For example, you can look up a product code in a list and return the matching product name, price, or stock status. The lookup range and result range can be on different parts of a sheet, and the result range can be to the left or right of the lookup range.

This flexibility is useful when a table’s layout changes or when the answer is not positioned to the right of the search column. With VLOOKUP, the search column must be the first column in the selected table, and a column index specifies which result to return. XLOOKUP instead refers directly to the lookup range and result range, so its formula is easier to interpret and less dependent on column positions.

XLOOKUP works for both vertical and horizontal searches. A vertical lookup searches down a column; a horizontal lookup searches across a row. You generally do not need separate functions for those two directions.

XLOOKUP Syntax and Arguments

The general 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 value to find, where to find it, and where to retrieve the answer. The remaining arguments let you control what happens when a value is missing and how the search is performed.

search_key: The value to look for, such as a product code in a cell.
lookup_range: The single row or column to search.
result_range: The corresponding single row or column from which to return a value. It should have the same number of positions as the lookup range.
missing_value: Optional text or another value to return if no match is found. If you omit it, an unsuccessful lookup returns an error.
match_mode: Optional setting for exact, approximate, or wildcard matching.
search_mode: Optional setting for the direction or method of the search.

Square brackets in the syntax indicate optional arguments; do not type the brackets into a formula. In the examples below, commas separate arguments. Depending on a spreadsheet’s locale, Google Sheets may display a different argument separator, so follow the separator used by your own sheet if you see a formula parse error.

A Basic XLOOKUP Example

Suppose product codes are in A2:A5, product names are in B2:B5, and the code to search for is entered in E2. With that cell layout, this formula returns the matching product name or a readable message if the code is absent:

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

XLOOKUP uses an exact match by default. If E2 contains a code found in A2:A5, Sheets returns the value in the same position in B2:B5. For instance, if the code in E2 is on the third row of the lookup range, the formula returns the name on the third row of the result range. If the code is not present, the formula returns Not found instead of the usual no-match error.

Keeping the lookup and result ranges aligned is important. If codes are in A2:A5 but names are in B3:B6, the ranges do not correspond to the same records and the result may be wrong even though the formula itself is accepted. Use ranges with matching dimensions and matching row or column order.

Exact, Approximate, and Wildcard Matching

For most ordinary lookups—employee IDs, order numbers, or exact product codes—the default exact match is the right choice. You can also set match_mode explicitly to make the intention clear. Match mode 0 means exact match. Mode 1 finds an exact match or, if there is none, the next larger value; mode -1 finds an exact match or the next smaller value. These approximate options can be useful for ordered thresholds, such as assigning a grade or rate based on a numeric range.

For example, assume minimum score thresholds 60, 70, 80, and 90 are in A2:A5, their grade labels are in B2:B5, and a student’s score is in E2. This formula looks for the exact threshold or the next larger threshold:

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

That direction matters: mode 1 searches for the next larger value, not the greatest threshold below the score. For a conventional score-band table in which each threshold represents the minimum score for its grade, mode -1 is often the appropriate approximate setting: it returns an exact threshold or the next smaller one. Check the meaning of your threshold table before selecting a mode, and test values at the boundaries.

Match mode 2 enables wildcard matching. An asterisk represents any number of characters, while a question mark represents one character. If A2:A5 contains descriptions, B2:B5 contains categories, and E2 contains a word or phrase to find within those descriptions, this formula searches for a description containing that text:

=XLOOKUP("*"&E2&"*",A2:A5,B2:B5,"No match",2)

Wildcard matching is not the same as a regular-expression search. If you need regular-expression behavior, use a function designed for that purpose rather than assuming XLOOKUP wildcards support regular-expression syntax.

Search Direction and Finding the Latest Match

By default, XLOOKUP searches from the first item toward the last. Search mode -1 reverses that direction and searches from last to first. This is handy when a log contains repeated IDs and you want the result from the latest matching row, provided the rows are arranged in chronological order.

For example, if IDs appear in A2:A100, dates or status values to return are in B2:B100, and the ID to find is in E2, this formula returns the result from the last matching occurrence in the range:

=XLOOKUP(E2,A2:A100,B2:B100,"Not found",0,-1)

Reverse search does not sort the data or determine which date is chronologically latest. It simply starts at the bottom of the specified range. If rows are not in date order, the last occurrence in the sheet may not be the newest record. The other search modes, 2 and -2, use binary searching and require the lookup data to be sorted in the appropriate order; for typical unsorted lists, stick with the default linear search.

Range Limits and Returning Results

In Google Sheets, XLOOKUP’s lookup range and result range are each a single row or column and need to line up in size. If you search a column of 100 IDs, the corresponding result range should cover 100 positions as well. Mismatched lengths or an accidentally shifted range can produce errors or, more subtly, return information associated with the wrong record.

If you need several fields for the matching record—such as a name, department, and location—one straightforward option is to use separate XLOOKUP formulas for each result column. You can also investigate array formulas when you want to return multiple results at once, but make sure the result range and the behavior you need are supported by the formula setup. For a simple one-value lookup, keep the formula ranges narrow and easy to audit rather than selecting unrelated columns.

Whole-column references can be convenient, but bounded ranges such as A2:A5000 make it clearer which records are included. In shared sheets, clear range boundaries also help prevent headings or footnotes from becoming unintended lookup values. When records are added often, consider how the ranges will expand so new entries are not left out.

Common XLOOKUP Problems

A no-match error is not always evidence that the value is missing. The lookup value may contain extra spaces, a different spelling, or a different data type. A number stored as text may not match a numeric value that looks identical on screen. Check the source cells, and consider cleaning inconsistent data rather than hiding every problem with a custom missing-value message.

Wrong result: Check that the lookup and result ranges start and end on corresponding rows or columns.
Unexpected no match: Check for spaces, punctuation, spelling differences, and numbers stored as text.
Approximate result seems off: Confirm whether you want the next larger or next smaller value, and inspect the relevant threshold boundaries.
Formula parse error: Check parentheses, commas or the separator required by the sheet’s locale, and quotation marks around text.
Wrong repeated record: Use reverse search only if the last occurrence in the selected range is the record you want.

Also distinguish a blank result from a missing match. If the matching row’s result cell is empty, XLOOKUP may return a blank-looking result even though it found the lookup key. A custom missing-value argument applies when no match is found; it is not a general replacement for an empty cell in the result range.

XLOOKUP Compared With VLOOKUP and INDEX/MATCH

Compared with VLOOKUP, XLOOKUP can search in either direction and does not rely on a numeric column index. If a new column is inserted into a table, a VLOOKUP column number may need adjustment; XLOOKUP’s separately specified result range can make the intended return column more apparent. VLOOKUP remains familiar and may be useful in older workbooks, but it is not required just because the data is arranged vertically.

INDEX/MATCH is another flexible approach: MATCH finds a position and INDEX returns the value at that position. It remains valuable in existing spreadsheets and in environments where XLOOKUP is unavailable. In Google Sheets, however, XLOOKUP can express many standard lookup tasks in one function, with an optional not-found value built into the formula.

Choose based on the people and software that need to use the sheet. If everyone works in current Google Sheets, XLOOKUP is a clear option for new lookup formulas. If a workbook must also be opened in older spreadsheet software, a more widely supported method may be safer, or the formula should be checked in the target program before distribution.

Google Sheets and Excel Compatibility

XLOOKUP is a Google Sheets function; it is not exclusive to Microsoft Excel. Microsoft Excel for Microsoft 365 and newer perpetual versions such as Excel 2021 also support XLOOKUP, although available features can vary with the specific Excel version. Older Excel releases, including Excel 2019 and Excel 2016, do not have native XLOOKUP support.

That distinction matters when downloading a Sheets file as an Excel workbook or sharing formulas with someone using an older Excel version. A formula that works in Sheets may not calculate in unsupported software. If compatibility is important, confirm the recipient’s version and test the exported workbook in that program. VLOOKUP or INDEX/MATCH may be preferable when broad support for older spreadsheet applications is a requirement.

Frequently Asked Questions

Is XLOOKUP available in Google Sheets?
Yes. XLOOKUP is a native Google Sheets function. It can search a row or column and return a corresponding result from another aligned row or column.

What is the default match type in Google Sheets XLOOKUP?
The default is exact matching. You can use the optional match_mode argument to request approximate or wildcard matching instead.

Can XLOOKUP search to the left?
Yes. Unlike VLOOKUP’s usual table arrangement, XLOOKUP lets you specify the lookup range and result range separately, so the result can be on either side of the lookup range.

How do I make XLOOKUP return the last match?
Set search_mode to -1. The function then searches from the last position in the specified lookup range toward the first; it does not sort the data or identify the newest date automatically.

Why does XLOOKUP return an error when the value is present?
Check for hidden spaces, text-versus-number differences, and a lookup range that does not actually include the value. Also verify that the result range is aligned with the lookup range and that the formula uses the correct separator for your sheet.

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.