XLOOKUP in Excel: Find a Value and Return Matching Data

Formulas & functions · Updated November 20, 2026

To find a value in Excel and return its matching result, use XLOOKUP: with product IDs in A2:A6, product names in B2:B6, and the ID to find in E2, enter =XLOOKUP(E2,A2:A6,B2:B6,"Not found"). XLOOKUP searches for E2 in column A and returns the value from the same row in column B; an exact match is the default.

What XLOOKUP does

XLOOKUP is a lookup function in Microsoft Excel. It connects a value in one list, such as a product code or employee number, with related information in another range, such as a product name or department. Unlike a simple search that only locates a cell, XLOOKUP can return the corresponding value for you.

The function is useful when a worksheet has a lookup table and you want to retrieve a particular field without manually scanning rows. For example, a sales sheet might contain product codes while a separate price list contains each code and its current price. XLOOKUP can find the code in the price list and bring the price into the sales sheet.

The function’s basic structure is XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode]). The first three arguments are the essentials: what to find, where to look, and what range to return a result from. The remaining arguments are optional settings for missing values, approximate or wildcard matching, and search direction.

Build a basic XLOOKUP formula

First identify the lookup value, the range containing it, and the aligned range containing the answer. In this example, A2:A6 contains product IDs, B2:B6 contains product names, and E2 contains P-104. The formula returns Notebook:

With product IDs in A2:A6, names in B2:B6, and P-104 in E2, enter:

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

Excel checks the IDs in A2:A6 for the value in E2. When it finds P-104, it returns the value from the corresponding position in B2:B6. If that ID is absent, the formula displays Not found rather than an error. The fourth argument is optional, but a clear message is often more helpful than the default #N/A result.

To enter the formula, select the cell where you want the answer, type an equals sign and the function, then select or type each range separated by commas. Press Enter. You can also start typing =XLOOKUP( and follow Excel’s function hints. If you copy the formula down, consider whether the lookup ranges should stay fixed. For a formula copied from row 2 down a column, absolute references can keep the table ranges from shifting:

With IDs in A2:A6, names in B2:B6, and a lookup ID in E2, use this version when copying down:

=XLOOKUP(E2,$A$2:$A$6,$B$2:$B$6,"Not found")

The dollar signs lock the table ranges, while E2 remains relative and can change to E3 or E4 as the formula is filled down.

How matching and returning work

By default, XLOOKUP looks for an exact match and searches from the first item to the last. It does not require the lookup column to be positioned to the left of the return column. The lookup and return ranges need to correspond: each lookup item should align with the result on the same row, or in the same position for horizontal ranges.

For example, you can search a department column and return a staff member’s ID from a column to its left. With employee IDs in A2:A10, department names in B2:B10, and the department to find in E2, enter:

=XLOOKUP(E2,B2:B10,A2:A10,"No match")

If E2 contains Finance, the formula returns the ID from column A on the row where column B says Finance. If several rows contain Finance, the default search returns the first match from the top. This leftward lookup is one reason XLOOKUP can be easier to maintain than VLOOKUP, which traditionally requires the lookup column to be at the left of its return range.

Return more than one result

XLOOKUP can return more than one column from the matching row. This is called a dynamic array result: Excel places the returned values in neighboring cells automatically, as long as those cells are empty. The output is said to spill. It is not necessary to write separate lookup formulas for each returned field.

With IDs in A2:A6, product names in B2:B6, prices in C2:C6, and the ID to find in E2, enter the formula in F2:

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

For P-104, Excel returns that row’s name and price into F2:G2. If a value or another formula blocks the spill area, Excel displays a #SPILL! error; clear the obstructing cells or move the formula. Dynamic array results are supported in current Microsoft 365 versions and Excel 2021 and later. In an older Excel version that does not support XLOOKUP, the formula will not work.

Choose exact, approximate, or wildcard matching

The match_mode argument controls what counts as a match. Leaving it blank, or using 0, requests an exact match. Use -1 to accept an exact match or the next smaller item, and 1 for an exact match or the next larger item. These options are useful for ordered thresholds, but the lookup data should be arranged so the result has the intended meaning.

For instance, suppose A2:A4 contains ascending minimum order quantities 0, 10, and 25, while B2:B4 contains the discount rate for each threshold. With the requested quantity in E2, a quantity of 18 should receive the rate for the 10-unit threshold:

With minimum quantities in A2:A4, corresponding rates in B2:B4, and the order quantity in E2, enter:

=XLOOKUP(E2,A2:A4,B2:B4,"No tier",-1)

The -1 setting selects an exact value if present or the next smaller value; for 18, that is 10. This is an illustrative lookup, not a recommendation for setting real prices or discounts. Confirm that the thresholds and rules in your own workbook are correct.

Use match_mode 2 for wildcard matching. An asterisk represents any number of characters, while a question mark represents one character. With descriptions in A2:A5, matching codes or names in B2:B5, and a partial term in E2, this formula returns the first description containing that term:

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

The formula uses ampersands to join the wildcard characters to E2. Wildcards are for text matching, not a substitute for checking that an exact numeric value is stored correctly.

Find the last occurrence

The search_mode argument controls the direction of the search. Its default, 1, searches from the first item to the last. Use -1 to search from the last item upward and return the most recent matching row in a list. This is helpful for repeated records, such as a log where the same customer appears more than once.

With transaction dates in A2:A100, customer names in B2:B100, and a customer name in E2, enter:

=XLOOKUP(E2,B2:B100,A2:A100,"No record",0,-1)

This returns the date from the bottommost matching row, assuming the rows are arranged in the order relevant to your question. It does not automatically identify the latest calendar date if the data are unsorted; it returns the last match by position. XLOOKUP also offers binary search modes for sorted data, but those require the lookup range to be sorted correctly. An incorrectly sorted range can produce misleading results, so ordinary first-to-last or last-to-first searches are safer for unsorted lists.

Common problems and how to fix them

  • #N/A: No exact match was found, or the value differs from the lookup entry. Add an if-not-found message, then check spelling, spaces, and the actual stored values.
  • #VALUE!: The lookup and return ranges may not have compatible dimensions. For a vertical lookup, use ranges with the same number of rows; for a horizontal lookup, use ranges with the same number of columns.
  • #SPILL!: A multi-cell result cannot expand because something occupies a destination cell. Clear the cells in the spill area or move the formula.
  • #NAME?: The Excel version may not support XLOOKUP, or the function name may be misspelled. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including Excel 2024; it is not available in Excel 2019 or Excel 2016.

A frequent source of an unexpected not-found result is a mismatch in data type. A number stored as text may look identical to a numeric value but not match it. Leading or trailing spaces in text can also prevent a match. Check the source cells and, where appropriate, standardize the data before relying on the lookup. Avoid changing a value blindly: leading zeroes in an ID, for example, may be meaningful and should be preserved as text.

XLOOKUP compared with VLOOKUP and INDEX/MATCH

VLOOKUP remains familiar and is useful in older workbooks, but its return column is specified by a column number within a table. Inserting or rearranging columns can make that number harder to interpret, and VLOOKUP generally searches only from the table’s first column toward columns on its right. XLOOKUP names the lookup and return ranges separately, supports a return range on either side, and defaults to exact matching.

INDEX and MATCH can also perform flexible lookups and are valuable when a workbook must run in Excel versions that predate XLOOKUP. However, they usually require a more involved formula. XLOOKUP is not automatically the right choice for every workbook: if you need compatibility with older Excel releases, check the audience’s software before replacing existing formulas. A workbook containing XLOOKUP may show a function-name error for someone using an unsupported version.

Availability and practical use

For Microsoft Excel on Windows, type XLOOKUP directly into a worksheet cell; no add-in or special setting is required in supported versions. Current Microsoft 365 editions of Excel on Windows, Mac, and the web support the function, although menus and the surrounding interface can differ by platform. The examples here use comma argument separators, as specified for the typical English-language setup; regional settings may use another list separator.

Before sharing a workbook, consider whether recipients use Excel 2021 or later or Microsoft 365. If not, choose a compatible alternative or provide a version of the workbook that does not depend on XLOOKUP. Also check formulas that return multiple columns: recipients need a version with dynamic array support for the spill behavior shown above.

Frequently Asked Questions

How do I use XLOOKUP to find a value in Excel?
Use =XLOOKUP(value_to_find,range_to_search,range_to_return,"Not found") as a pattern. Replace the placeholders with a cell or value and matching worksheet ranges. XLOOKUP returns the result aligned with the matching item.

Does XLOOKUP find an exact match by default?
Yes. Its default match mode is exact, and its default search direction is from the first item to the last. You can specify other match or search modes when the task calls for them.

Can XLOOKUP look to the left?
Yes. Set the lookup range to the column containing the value to find and the return range to the column containing the answer. The return column can be to the left or right of the lookup column.

How do I return a message when XLOOKUP finds nothing?
Enter the message as the fourth argument, known as if_not_found. For example, in a lookup whose value is in E2, whose search range is A2:A6, and whose return range is B2:B6, =XLOOKUP(E2,A2:A6,B2:B6,"No match") displays No match when the value is absent.

Why is XLOOKUP not working in my version of Excel?
Check that your copy supports the function. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not Excel 2019 or 2016. If it is supported, check for misspelled function names, range-size mismatches, hidden spaces, and number-versus-text differences.

Can XLOOKUP return the last matching value?
Yes. Set search_mode to -1, the sixth argument, to search from the last item upward. This returns the last match by position in the range, which is not necessarily the most recent date unless the data order makes it so.

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.