XLOOKUP Method Examples: Formulas That Work

Formulas & functions · Updated November 3, 2026

XLOOKUP finds a value in one range and returns 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, enter =XLOOKUP(D2,A2:A6,B2:B6,"Not found") in E2; if D2 contains an ID for “Desk Lamp,” E2 returns Desk Lamp.

What XLOOKUP does

XLOOKUP is a lookup function in Microsoft Excel for Microsoft 365, Excel 2021, and Excel 2024. It searches a specified range or array for a value, then returns a value from a corresponding position in a second range or array. The ranges do not have to be arranged in a particular order, and the return range can be to the left or right of the search range.

This flexibility addresses two familiar limitations of older lookup methods. VLOOKUP generally searches the first column of a table and returns results from columns to its right; INDEX and MATCH can handle more layouts, but usually require a more involved formula. XLOOKUP makes the search range and return range separate arguments, so the formula often reads more clearly.

XLOOKUP is available in current Microsoft 365 versions for Windows, Mac, and Excel for the web. The function is not available in Excel 2019 or older perpetual versions; those versions may show a #NAME? error. If a workbook will be opened in an older Excel edition, check compatibility before relying on XLOOKUP.

How the arguments work

The function takes a value to find, the range to search, and the range to return a result from. It can also take an optional value for when nothing matches, a match mode, and a search mode. In everyday use, the first three arguments are the essentials; the optional arguments let you control special cases.

  • Lookup value: the value to search for, such as an employee ID entered in a cell.
  • Lookup array: the one-dimensional range or array to search.
  • Return array: the corresponding range or array containing the result.
  • If not found: optional text or another value to return when there is no match, rather than an error.
  • Match mode: exact match by default, or approximate and wildcard matching when requested.
  • Search mode: searches from first to last by default, with options to search backward or use a binary search on sorted data.

With the default settings, XLOOKUP seeks an exact match and searches from the first item to the last. Unlike VLOOKUP, it does not need a column-number argument, and inserting a column elsewhere in a worksheet does not change which return range the formula refers to.

Example: Find a value from an ID

Suppose A2:A6 contains employee IDs 101, 102, 103, 104, and 105, while B2:B6 contains the corresponding names Ana, Ben, Mina, Omar, and Priya. Put the ID to look up in D2; if D2 contains 103, enter this in E2:

With IDs in A2:A6, names in B2:B6, and the lookup ID in D2, use =XLOOKUP(D2,A2:A6,B2:B6,"Not found"). The result is Mina, because 103 is the third item in the ID range and Mina is the third item in the names range.

The ranges line up by position: the first item in the lookup range corresponds to the first item in the return range, the second to the second, and so on. Their shapes must be compatible. For a single-column search, use a single-column return range of the same height, or a wider return range of the same height when you intentionally want multiple columns back.

Example: Return a value from the left

XLOOKUP can return a result from either side of the lookup range. Imagine product names in A2:A5 and product codes in B2:B5. If the code is in D2, you can search the code column and return the product name from the column to its left.

With names in A2:A5, codes in B2:B5, and the code to find in D2, enter =XLOOKUP(D2,B2:B5,A2:A5,"No match"). If D2 contains code P-204 and that code belongs to “Desk Lamp,” the formula returns Desk Lamp. The order of the lookup and return ranges—not their left-to-right position on the sheet—determines what XLOOKUP searches and returns.

Example: Return multiple columns

A lookup can return more than one column when the return array includes multiple columns. For instance, a staff table might have employee IDs in A2:A5, departments in B2:B5, and office locations in C2:C5. If you want both the department and location for an ID in E2, select F2 and enter the lookup there.

With IDs in A2:A5, departments and locations in B2:C5, and the ID to find in E2, use =XLOOKUP(E2,A2:A5,B2:C5,"No match"). If the matching row contains “Sales” in column B and “London” in column C, the results spill into F2 and G2 as Sales and London. Leave the spill destination cells empty; a value blocking the output can cause a #SPILL! error. Dynamic-array behavior is available in Microsoft 365 and Excel 2021 or later.

Example: Choose how a match is found

Exact matching is suitable for IDs, names, and other values that should match precisely. For wildcard searches, set match mode to 2. The asterisk represents any number of characters, while the question mark represents one character. For example, a search value such as West* can match text beginning with West.

With region labels in A2:A6, contact names in B2:B6, and a pattern such as West* in D2, enter =XLOOKUP(D2,A2:A6,B2:B6,"No match",2). The result is the contact for the first matching region in the search range. If your search text needs a literal asterisk or question mark rather than a wildcard, prefix that character with a tilde.

Approximate matching is useful for thresholds, such as a rate table whose first column lists the minimum value for each band. Match mode -1 finds an exact match or the next smaller item; match mode 1 finds an exact match or the next larger item. For next-smaller matching, sort threshold values in ascending order so the bands behave predictably.

With ascending income thresholds in A2:A4 (0, 10000, and 30000), corresponding rates in B2:B4 (0%, 5%, and 10%), and income 24000 in D2, enter =XLOOKUP(D2,A2:A4,B2:B4,"No band",-1). The result is 5%, because 10000 is the greatest threshold that does not exceed 24000. This is an illustrative lookup example, not tax guidance; real rules and rates depend on the relevant jurisdiction and circumstances.

Example: Find the last matching entry

By default, XLOOKUP returns the first match it encounters. When a list records repeated events over time, you may instead want the latest matching row, assuming newer entries appear later in the range. Set search mode to -1 to search from the last item toward the first; keep match mode at 0 for an exact match.

With transaction IDs in A2:A10, statuses in B2:B10, and the ID to find in D2, enter =XLOOKUP(D2,A2:A10,B2:B10,"No match",0,-1). If the ID appears in rows 3, 6, and 9, the result comes from row 9. This returns the last occurrence in the range, not automatically the latest date: the rows must actually be ordered so that the final occurrence represents the latest record.

Common errors and how to fix them

  • #NAME?: the Excel version may not support XLOOKUP, or the function name may be misspelled. Confirm that the workbook is open in Microsoft 365, Excel 2021, or a later supported version.
  • #N/A: there is no match and the formula has no if-not-found argument. Check for extra spaces, different spellings, or a number stored as text in one range but as a number in the other. Add an if-not-found value when a clear message is preferable.
  • #VALUE!: the lookup and return arrays may have incompatible dimensions. Check that the rows correspond and that the return range has the intended height.
  • #SPILL!: a multi-column result cannot expand into one or more occupied cells. Clear the cells where the results need to appear, or use a single-column return range.
  • Unexpected result: duplicates return the first match by default, and approximate matching follows its selected mode. Verify the data order, duplicate entries, and optional match or search settings.

Also check that the selected ranges cover all intended records. A formula that searches A2:A20 will not find an item added later in row 21. Excel tables can help keep lookup ranges aligned as records are added, although table references require using the table and column names that exist in your workbook.

XLOOKUP compared with VLOOKUP and alternatives

Choose XLOOKUP when you want a direct exact-match search, need to return data from a column on either side of the search range, or want an explicit not-found result. It can also return multiple columns in supported versions. For a simple lookup, the formula is usually easier to inspect than a combination of older functions.

VLOOKUP remains useful in older workbooks and Excel versions, but its search column must be the first column in the table array, and its return-column number can become fragile if the table layout changes. INDEX and MATCH can search in either direction and work in older Excel versions, but their formulas typically take more setup. For an older workbook, use the method supported by the recipients’ Excel versions rather than assuming XLOOKUP will calculate everywhere.

For a lookup that depends on both a row label and a column heading, two XLOOKUP functions can be nested: one can select a column, and the other can find the desired row in that column. For more complex data tasks, Excel also has functions such as FILTER, but FILTER returns rows meeting conditions rather than simply returning a corresponding value from a single match. These newer functions require Excel 2021 or Microsoft 365, so check version compatibility when sharing files.

Frequently Asked Questions

Does XLOOKUP use exact match by default?
Yes. Its default match mode is exact, so you usually do not need to specify a match mode for IDs, names, and other exact lookups. It searches from first to last by default.

Can XLOOKUP search to the left?
Yes. Set the lookup array to the range containing the value to find and the return array to the range containing the result. The return range can be on the left or right.

Why does my XLOOKUP return #N/A?
Usually, there is no exact match. Check for spaces, inconsistent spelling, and number-versus-text differences, and confirm that the lookup range includes the value. You can also supply an if-not-found result to display a useful message.

Can XLOOKUP return more than one result column?
It can return multiple columns when its return array includes them, and supported dynamic-array versions spill the results into adjacent cells. Make sure those cells are empty and that your Excel version supports dynamic arrays.

How do I make XLOOKUP return the last match?
Set its search mode to -1 while leaving the match mode at 0 for an exact match. The function then searches from the end of the selected range toward the beginning, so confirm the range order matches what you mean by “last.”

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.