XLOOKUP in Google Sheets: How to Find Matching Data

In Google Sheets, use XLOOKUP to find a value in one range and return the corresponding value from another: with product IDs in A2:A6, stock counts in B2:B6, and the ID to search in D2, enter =XLOOKUP(D2,A2:A6,B2:B6,"Not found") in E2. The function returns the stock count for the matching ID, or “Not found” if there is no match. You need a Google Sheets file with a lookup range and a result range; the ranges must line up.

What You’ll Need

A Google account and a spreadsheet open in Google Sheets in a web browser.
A lookup range containing the values you want to search, such as product IDs, names, or order numbers.
A result range containing the corresponding information you want returned. Keep the two ranges the same length and in matching row or column order.
A search value, either typed into a cell or entered directly into the formula.

XLOOKUP is available as a Google Sheets function; it is not necessary to install an add-on. The examples below use comma separators. Depending on your spreadsheet’s locale, Sheets may expect semicolons instead, so follow the separator used by other formulas in that file if a pasted formula is rejected. Excel has an XLOOKUP function too, but the instructions here are for Sheets in a browser.

Step-by-Step

Arrange the data in corresponding rows or columns. Put the values to search in one continuous range and the answers beside them or in a parallel range. For example, enter product IDs in A2:A6 and stock counts in B2:B6. Make sure each count belongs to the ID in the same row. XLOOKUP does not require the search column to be the first column of a larger table, and it can look in either direction.
Choose a cell for the search value. Put the ID you want to find in D2. For example, use P-103 if the IDs in A2:A6 are P-101, P-102, P-103, P-104, and P-105. You can also put a name or other value in this cell, as long as its type and spelling correspond to the values in the lookup range. A separate input cell makes it easy to reuse the formula with different searches.
Select the cell where the answer should appear. Click E2, or another empty cell that does not overlap your source data. A basic XLOOKUP has this structure: =XLOOKUP(search_key,lookup_range,result_range). The search_key is the value to find; lookup_range is where to search; result_range contains the corresponding answer. Keep both ranges the same length. You can use a vertical range for a vertical list or a horizontal range for a horizontal list.
Enter the formula with an exact-match fallback. With IDs in A2:A6, stock counts in B2:B6, and the value to search in D2, enter this in E2: =XLOOKUP(D2,A2:A6,B2:B6,"Not found"). In this example, if P-103 is in D2 and its row has 18 in B4, E2 displays 18. The fourth argument supplies text to show when Sheets cannot find the value, which is more informative than an error. The default match is exact, so this is appropriate for IDs and names.
Press Enter and check the result against the source row. Confirm that the returned value is on the same row as the matching ID. If the result is unexpected, inspect the IDs and ranges before changing the match options. If you see “Not found,” verify that D2 contains an ID that appears in A2:A6. Also confirm that the formula points to the intended columns and that the source ranges have not been shifted.
Use cell references so the search can change. Replace the value in D2 with another valid product ID. The formula in E2 recalculates and returns that row’s count. To fill the formula down for a list of search values in D2:D10, select E2 and drag the small blue fill handle down. For this basic example, Sheets adjusts the ranges as you fill, which may be undesirable: use locked range references if the lookup list must stay fixed. With IDs in A2:A6, counts in B2:B6, and each search value in D2 and below, enter this in E2 before filling down: =XLOOKUP(D2,$A$2:$A$6,$B$2:$B$6,"Not found"). The dollar signs keep the source ranges fixed while the search reference changes to D3, D4, and so on.
Return a different field by changing the result range. If IDs are in A2:A6, product names in B2:B6, and the search ID is in D2, enter this in E2 to return a name instead of a count: =XLOOKUP(D2,A2:A6,B2:B6,"Not found"). To return a value from another column, use that column’s matching rows as result_range. For example, if the category is in C2:C6, use C2:C6 as the result range. Never swap in a range whose row order differs from the lookup range, or Sheets can return an answer belonging to another record.
Choose an optional match or search mode only when needed. XLOOKUP uses exact matching by default. Its optional fifth argument, match_mode, can be 0 for exact match, 1 for exact or next larger value, -1 for exact or next smaller value, or 2 for wildcard matching. Its optional sixth argument, search_mode, can search from first to last (1, the default) or last to first (-1). For example, a reverse search can return the latest matching entry in a list with duplicates. With IDs in A2:A6, dates or other associated values in B2:B6, and the search ID in D2, enter this in E2 to find the last occurrence: =XLOOKUP(D2,A2:A6,B2:B6,"Not found",0,-1). Use binary search modes only when the lookup range is correctly sorted in the required order.

Common Mistakes

Using ranges of different sizes: A2:A6 contains five rows, so its result range should also contain five corresponding rows, such as B2:B6. A range with a different number of cells can cause an error or an unusable result.

Returning a neighboring but mismatched range: Check that the first lookup value and first result value belong together, and that every subsequent pair stays aligned. Sorting just one of the two columns breaks that relationship. Sort the complete data table together.

Expecting an approximate match by default: XLOOKUP’s default is exact matching, which is usually right for product codes and names. If you need the next larger or smaller value, set match_mode deliberately and understand how the values are ordered. An incorrect match mode can produce a plausible but wrong answer.

Overlooking extra spaces or different data types: P-103 stored as text may not behave like a number stored as a numeric value, and an ID with a trailing space may not equal the clean version. Check the source and search cells for stray spaces, inconsistent formatting, or numbers imported as text.

Typing the formula in a cell that already contains data: If the result range returns several columns or rows, the output may need empty neighboring cells. Clear the cells where the result is meant to expand, rather than overwriting useful data.

Tips

Use a clear missing-value message, such as “No matching ID,” when other people will read the sheet. It makes an absent record easier to distinguish from an empty result cell. If a blank is the intended result when no match exists, you can use an empty text value as the fourth argument. With IDs in A2:A6, corresponding names in B2:B6, and a search value in D2, enter this in E2: =XLOOKUP(D2,A2:A6,B2:B6," "). This returns a space, not a truly empty cell; use "" as the fourth argument if you want an empty string displayed instead.

Keep the lookup and result ranges limited to the data you need, especially in large spreadsheets. Fixed ranges make a formula’s scope obvious and help avoid accidentally including headers. If you add rows later, update the ranges or use a source layout that expands as new records are added. Use absolute references with dollar signs when copying a formula so the source ranges remain fixed.

For a list with duplicate keys, ordinary exact XLOOKUP returns the first match it encounters. If the newest or last matching record is required, use search_mode -1 and make sure the list’s order reflects what “last” means. If you need to find a partial text pattern, wildcard matching is available with match_mode 2; use * for any sequence of characters and ? for a single character. Test a wildcard on a few known entries before relying on it for a full report.

For a lookup that returns several adjacent fields, use a result range spanning those fields, and leave enough empty cells for the returned values to appear. This can be handy when one key should return a name and category together. Check that the output area is clear and that each returned column is in the intended order.

Troubleshooting

The formula shows #N/A or your missing-value message: The searched value was not found as an exact match. Compare the search key with the source entry, including spaces and punctuation. If the data was imported, check whether the value is stored as text in one place and as a number in another. If the value truly is absent, the fallback message is working as intended.

Sheets rejects the formula as a parse error: Confirm that the function name is XLOOKUP, that each opening parenthesis has a closing one, and that text is enclosed in straight double quotes. If the file’s locale uses semicolons between formula arguments, replace the commas with semicolons. Also check that the spreadsheet is open in Google Sheets rather than a viewer that cannot edit formulas.

The result is from the wrong row: Inspect the lookup and result ranges cell by cell. They need the same orientation and corresponding positions. If rows were sorted, sort all related columns together. Check that the formula has not shifted after copying it; dollar signs around the source ranges prevent that movement.

The formula returns an unexpected match: Verify the fifth and sixth arguments if you added them. Match mode 1 and -1 allow a nearest-value match, while wildcard mode 2 interprets * and ? as pattern characters. Search mode -1 starts from the end, so duplicate keys can return a different record than the default first-to-last search. For straightforward IDs, remove optional modes and rely on the default exact match.

A multi-cell result does not appear: Clear any existing contents in the cells where the result needs to expand. Check that the result range itself contains the desired fields and that the formula is not blocked by merged cells or other occupied cells.

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 add-on is required.

What is the basic XLOOKUP syntax in Sheets?
Use =XLOOKUP(search_key,lookup_range,result_range). The first argument is what you want to find, the second is where to search, and the third is the corresponding range to return from.

Does XLOOKUP search for an exact match by default?
Yes. Its default match mode is exact. You can use an optional match mode for a nearest-value or wildcard lookup, but exact matching is generally the best choice for unique IDs.

Can XLOOKUP look to the left?
Yes. Unlike a lookup that requires the return column to be to the right, XLOOKUP can search one range and return a corresponding range located to its left, provided the entries line up.

How do I return the last matching result?
Set the sixth argument, search_mode, to -1. That tells XLOOKUP to search from the last entry toward the first. This is useful when the lookup range contains duplicates and the final occurrence is the one you need.

Is XLOOKUP in Google Sheets different from Excel?
The core purpose and familiar argument pattern are similar in both programs, but this guide uses Google Sheets in a browser. Formula separators can depend on locale, and menu or interface details are not interchangeable between Sheets and Excel.

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.