XLOOKUP Formula in Excel: A Practical Step-by-Step Guide

Formulas & functions · Updated November 20, 2026

In Excel for Microsoft 365, XLOOKUP finds a value in one range and returns the corresponding value from another: enter =XLOOKUP(E2,A2:A6,B2:B6,"Code not found") to look up the code in E2 within A2:A6 and return its match from B2:B6. XLOOKUP uses exact matching by default, can search in either direction, and lets you choose what to show when there is no match.

What XLOOKUP Does

XLOOKUP is a lookup function: it searches a row or column for a value and retrieves a related value from a corresponding row or column. For example, a product list might have product codes in column A and prices in column B. Searching for a code returns its price without requiring the code column to sit to the left of the price column.

Microsoft introduced XLOOKUP as a more flexible successor to familiar lookup approaches such as VLOOKUP and HLOOKUP. It is available in Microsoft 365 and Excel 2021 or later, including Excel for the web. It is not available in Excel 2019 or Excel 2016. If you share a workbook with someone using an older version, the formula may display as an unsupported-function error for them.

Understand the Arguments

The function has six arguments, in this order: the value to find, the range to search, the range to return a result from, an optional value for a missing match, an optional match mode, and an optional search mode. The last three are optional, so a straightforward exact lookup needs only the first three.

  • Lookup value: The item you want to find, such as a product code in a cell.
  • Lookup array: The single row or column Excel searches.
  • Return array: The row or column containing the result. It must line up in size with the lookup array.
  • If not found: Text or another value to show if there is no match. If omitted, XLOOKUP returns #N/A.
  • Match mode: Controls exact, approximate, or wildcard matching.
  • Search mode: Controls the direction and, in one mode, the search method.

Separate arguments with commas in the formulas below. Some regional Excel settings use semicolons instead; use the separator configured for your installation if Excel rejects a correctly structured formula.

Build a Basic Exact-Match Lookup

First make sure the search values and results are arranged in matching rows. In this example, A2:A6 contains P-104, P-208, P-315, P-422, and P-517; B2:B6 contains their prices: 12.50, 18.75, 9.40, 24.00, and 16.20. Cell E2 contains P-315. With that layout, enter this formula in F2:

=XLOOKUP(E2,A2:A6,B2:B6,"Code not found")

The result is 9.4, which Excel may display as $9.40 if F2 is formatted as currency. XLOOKUP compares E2 with the codes in A2:A6 and returns the value from the same position in B2:B6. The optional message means a missing code produces “Code not found” instead of #N/A.

  1. Place the value to search for in a cell, or use a fixed value directly in the formula.
  2. Select a result cell and type =XLOOKUP(.
  3. Choose the lookup value, lookup range, and aligned return range, separating them with commas.
  4. Optionally add a not-found message, close the parenthesis, and press Enter.

Use ranges that cover the same number of rows or columns. A vertical lookup range paired with a return range of a different height can return a size mismatch error. If you copy the formula down, consider whether the ranges should stay fixed; references such as A$2:A$6 can keep the row numbers anchored while the formula moves.

Look Left, Up, or Return Multiple Columns

Unlike VLOOKUP, XLOOKUP does not require the lookup column to be the first column of a table. For a lookup in the other direction, suppose employee names are in A2:A6, staff IDs are in B2:B6, and E2 contains the ID to search. Enter this in F2:

=XLOOKUP(E2,B2:B6,A2:A6,"ID not found")

The formula searches the IDs and returns the name from the corresponding row, even though the result column is to the left of the search column. The same principle works horizontally: use a row as the lookup array and a corresponding row as the return array.

XLOOKUP can also return more than one column at once. Suppose product codes are in A2:A6, prices are in B2:B6, and departments are in C2:C6. If E2 contains a code, put this formula in F2:

=XLOOKUP(E2,A2:A6,B2:C6,"Code not found")

When a match is found, Excel returns both the price and department into adjacent cells, provided the cells to the right are empty. This is called spilling. If a cell in the spill area is occupied, Excel may show #SPILL!; clear the obstructing cell or move the formula.

Exact, Approximate, and Wildcard Matches

Exact matching is XLOOKUP’s default, so you do not have to add a match-mode argument for ordinary codes, names, or IDs. The optional match mode is useful when a value can fall between breakpoints or when you need to search for a text pattern:

  • 0: Exact match. This is the default.
  • -1: Exact match, or the next smaller item.
  • 1: Exact match, or the next larger item.
  • 2: Wildcard match, supporting patterns such as an asterisk for any number of characters and a question mark for one character. Use a tilde to escape a wildcard character when you mean it literally.

For example, an approximate lookup can assign a category based on a threshold. Suppose A2:A5 contains ascending minimum scores 0, 50, 70, and 90; B2:B5 contains Basic, Standard, Plus, and Pro; and E2 contains 68. With this layout, enter in F2:

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

The result is Standard because 50 is the largest threshold not greater than 68. For next-larger matching, mode 1 selects the smallest value that is at least as large as the lookup value. For approximate lookups, understand how the thresholds are ordered and what happens at the low and high ends; an unexpected nearest value can otherwise look plausible but be wrong.

Search Direction and Duplicate Values

By default, XLOOKUP searches from the first item to the last and returns the first match. If a list contains repeated IDs or names and you want the most recent matching entry at the bottom of the list, set the search mode to -1. For example, suppose IDs are in A2:A100, related status values are in B2:B100, and E2 contains the ID. Use this in F2:

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

Here, 0 explicitly requests an exact match and -1 searches from last to first, so the returned status comes from the last matching row. This does not sort or change the data; it only changes which duplicate is found first. The other search-mode options include a forward search and binary searches for sorted data. Binary search modes require the lookup range to be sorted correctly; using them on unsorted data can produce invalid results, so ordinary searches are safer for most worksheets.

Use XLOOKUP with Excel Tables

For a growing list, an Excel Table can be easier to maintain than fixed cell ranges. Select the data and choose Insert > Table, or use Ctrl+T on Windows (Command+T on Mac in Excel). Give the table clear column names, such as ProductCode and Price. Structured references make formulas easier to read, and table columns expand as you add rows.

With a table named Products, ProductCode values in its ProductCode column, prices in its Price column, and a lookup value in E2, enter this in F2:

=XLOOKUP(E2,Products[ProductCode],Products[Price],"Code not found")

Excel may automatically fill a formula down a table column when you enter it in a calculated column. Check the result when adding rows or changing table names, especially if other people maintain the workbook. Table names and column headings must match the names used in structured references.

Common Errors and Troubleshooting

  • #N/A: No exact match was found and no alternative not-found value was supplied. Check for misspellings, extra spaces, different data types, or an incorrect lookup range.
  • #VALUE!: The lookup and return arrays may have incompatible dimensions, or an argument may be invalid. Confirm the ranges line up.
  • #SPILL!: A multi-cell result cannot occupy the cells it needs. Clear the spill area or move the formula.
  • #NAME?: Excel may not recognize XLOOKUP because the version is too old, or because the function name or formula was typed incorrectly.
  • A match looks missing: Numbers stored as text do not always match numeric values, and spaces can make two text strings different. Check the source data and normalize it where appropriate.

Adding an “if not found” message helps make a report easier to read, but it can also conceal a data-quality problem if used carelessly. During setup, leaving the default #N/A can make missing matches visible. Once the lookup has been verified, choose a message that makes sense to the worksheet’s users.

XLOOKUP Compared with VLOOKUP and INDEX/MATCH

VLOOKUP searches down the first column of a selected table and returns a value from a column to its right. Its column-number argument can be fragile if columns are inserted, and it cannot return a value from a column to the left without rearranging the data. XLOOKUP names the search and result ranges separately, so it can look left or right and does not depend on a column index number.

INDEX/MATCH can also look in either direction and remains useful in older Excel versions where XLOOKUP is unavailable. It requires combining two functions, while XLOOKUP expresses the search and return ranges in one function. If a workbook must work in Excel 2019 or 2016, consider a compatible alternative such as INDEX/MATCH or VLOOKUP and check that the chosen formula behaves as intended there.

Frequently Asked Questions

Is XLOOKUP exact match by default?
Yes. Unless you set a different match mode, XLOOKUP looks for an exact match. If no match is found, it returns #N/A unless you provide an “if not found” value.

Can XLOOKUP return a value to the left?
Yes. The return array can be to either side of the lookup array. Specify the range to search first and the corresponding result range second.

Why is XLOOKUP not working in my version of Excel?
XLOOKUP is included in Microsoft 365 and Excel 2021 or later, but not Excel 2019 or Excel 2016. In an older version, use a compatible lookup method such as INDEX/MATCH or VLOOKUP.

Does XLOOKUP find the last duplicate?
Not with its default search direction: it returns the first match from the top. Set the search-mode argument to -1 to search from the bottom and return the last matching item.

Can XLOOKUP return more than one result column?
Yes. Set the return array to multiple adjacent columns. Excel can spill the matching row of results into neighboring cells, which must be clear for the formula to work.

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.