XLOOKUP Tutorial: Examples, Match Modes, and Tips

In Excel for Microsoft 365 on Windows, XLOOKUP searches one range and returns the corresponding value from another: 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). If E2 contains P-104, the result is Cable. XLOOKUP uses an exact match by default, can search in either direction, and can return a custom message when there is no match.

What XLOOKUP does and when to use it

XLOOKUP is a lookup function for finding a value in one range and returning a related value from another. For example, you can look up an employee ID in a staff list and return a name, or find a product code and return its price. Unlike older lookup formulas, it does not require the lookup column to be the first column in the selected range, and it does not need a column number to specify which result to return.

That makes it useful for everyday tasks such as matching order numbers, bringing details into a report, or assigning a category based on a score. It also lets you specify what to show if nothing matches, choose approximate or wildcard matching, and search from the bottom of a list upward. XLOOKUP works in Microsoft 365 and Excel 2021 or later, including Excel for the web. Excel 2019 and Excel 2016 do not support it; a workbook using XLOOKUP may show a #NAME? error in those versions.

How the XLOOKUP formula works

The basic syntax is =XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode]). The first three arguments are required. The lookup value is what you want to find; the lookup array is where Excel searches; and the return array contains the corresponding result. The optional arguments let you control missing results, matching rules, and search direction.

In a sample product list, suppose A2:A6 contains P-101, P-102, P-103, P-104, and P-105, while B2:B6 contains Notebook, Pen, Folder, Cable, and Lamp. If E2 contains P-104, this formula returns Cable:

With IDs in A2:A6, product names in B2:B6, and the requested ID in E2, enter =XLOOKUP(E2,A2:A6,B2:B6). The result is Cable.

The lookup and return ranges should line up: in this example, both have five rows, and each row in B corresponds to the ID on the same row in A. They do not have to be adjacent, and the lookup range can be either a column or a row. For instance, you could search across a row of month names and return a value from a different row.

Examples: custom not-found messages and multiple results

By default, a missing match produces #N/A. The fourth argument lets you replace that error with a clearer message. This can make a report easier to read, but consider whether you want to distinguish a truly missing record from a misspelled lookup value.

With product IDs in A2:A6, product names in B2:B6, and a requested ID in E2 that is absent from A2:A6, enter =XLOOKUP(E2,A2:A6,B2:B6,"Not found"). If E2 contains P-999, the formula returns Not found.

XLOOKUP can also return more than one adjacent column. Excel 2021 and Microsoft 365 support this behavior: the results spill into neighboring cells, so those cells need to be empty. If the names are in B2:B6 and prices in C2:C6, one formula can return both for the matching ID.

With IDs in A2:A6, names and prices in B2:C6, and the requested ID in E2, enter =XLOOKUP(E2,A2:A6,B2:C6). If E2 is P-104, Excel returns Cable and the corresponding price from C5 in the cells to the right of the formula.

Exact, approximate, and wildcard match modes

XLOOKUP uses exact matching by default, so most basic lookups do not need a match-mode argument. The fifth argument changes how Excel treats values that are not an exact match:

0 means exact match. This is the default.
-1 means exact match or the next smaller item.
1 means exact match or the next larger item.
2 enables wildcard matching, including * for any number of characters and ? for one character.

Approximate matching is useful for bands and thresholds. For example, suppose E2:E5 contains the ascending minimum scores 0, 60, 70, and 80, while F2:F5 contains the labels Basic, Bronze, Standard, and Premium. With a score of 73 in H2, the next-smaller option selects the 70 threshold and returns Standard.

With ascending thresholds in E2:E5, labels in F2:F5, and the score in H2, enter =XLOOKUP(H2,E2:E5,F2:F5,"No band",-1). If H2 is 73, the result is Standard. For approximate lookups, arrange the thresholds in the order that matches the chosen rule and check boundary values such as 59, 60, and 79. This helps confirm that a score falls into the intended band.

Wildcard matching can find text when only part of the value is known. For instance, searching for a name that begins with Jo can match Jordan or Joseph. With names in A2:A20 and corresponding departments in B2:B20, and the text Jo in E2, enter =XLOOKUP(E2&"*",A2:A20,B2:B20,"No match",2). The asterisk allows any characters after Jo. If you need a literal asterisk or question mark in the search text, prefix that wildcard character with a tilde.

Search direction and finding the latest match

The sixth argument controls search direction. Its default, 1, searches from the first item to the last. Use -1 to search from the last item toward the first. This is handy when a list contains repeated IDs and you want the last matching entry, such as the latest transaction in a date-sorted log. It returns the last match in the range; it does not independently check which date is newest.

With transaction IDs in A2:A100, transaction dates or details in B2:B100, and an ID to find in E2, enter =XLOOKUP(E2,A2:A100,B2:B100,"No match",0,-1). The formula returns the value from the last row containing that ID. If the range is not sorted by date, the last row may not represent the latest date, so sort the data appropriately or use a different method to establish which record is newest.

XLOOKUP also offers binary search modes, 2 for an ascending lookup array and -2 for a descending one. These modes require the data to be sorted correctly. If it is not, results can be invalid, so the ordinary search modes are safer for unsorted lists.

Looking left and matching more than one condition

Because the return array is specified separately, the result can be to the left or right of the lookup array. This avoids the left-to-right restriction associated with VLOOKUP. For example, if unique prices are in C2:C6 and their IDs are in A2:A6, you can search by price and return the ID.

With prices in C2:C6, IDs in A2:A6, and a price to find in E2, enter =XLOOKUP(E2,C2:C6,A2:A6,"No match"). If E2 contains 14.99 and that price belongs to P-104, the result is P-104. If prices are repeated, XLOOKUP returns the first matching ID unless you change the search direction.

You can combine conditions by multiplying logical tests. In Excel, each comparison produces TRUE or FALSE, and multiplication treats TRUE as 1 and FALSE as 0. A row that meets both tests produces 1. Suppose A2:A6 has IDs, B2:B6 has product names, and C2:C6 has prices; E2 contains an ID and F2 a product name.

With IDs in A2:A6, names in B2:B6, prices in C2:C6, and the two conditions in E2 and F2, enter =XLOOKUP(1,(A2:A6=E2)*(B2:B6=F2),C2:C6,"No match"). If E2 is P-104 and F2 is Cable, the result is that row’s price. If no row meets both conditions, the formula returns No match. This approach assumes the combined criteria identify the intended row; duplicates that meet both tests still return the first match.

Common problems and how to fix them

#N/A appears: The lookup value was not found as an exact match. Check spelling, extra spaces, punctuation, and whether the value exists. Add an if_not_found message only if hiding the error is appropriate.
The formula returns the wrong row: Duplicate lookup values may exist. Standard XLOOKUP returns the first match; use reverse search if you need the last match, or make the lookup criteria more specific.
A result is unexpectedly missing: Numbers stored as text may not match numbers stored as numeric values, even when they look alike. Check the underlying cell types and standardize the data rather than relying on visual appearance.
#SPILL! appears: A multi-column result needs clear cells in its spill area. Remove blocking content, including spaces or formulas, and make sure merged cells are not obstructing the output.
Approximate results seem wrong: Verify the match mode and threshold order. Binary search modes are especially sensitive to sorting; use the regular first-to-last or last-to-first search when the range is unsorted.
The formula shows #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, but not Excel 2019 or 2016.

XLOOKUP compared with VLOOKUP and INDEX/MATCH

VLOOKUP remains familiar and may be necessary when a workbook must work in older Excel versions. However, it searches the first column of a table and returns a value to its right using a column index. Inserting or rearranging columns can make that index fragile. XLOOKUP names the lookup and return ranges directly, can look left, defaults to exact matching, and includes a not-found argument.

INDEX/MATCH is another flexible option that can look in either direction and is supported in older Excel versions. It can be useful for compatibility or for existing workbooks built around that pattern, but the formula usually combines two functions. For new work in a supported version, XLOOKUP is often easier to read. These functions are not interchangeable in every workbook: if other users may open the file in Excel 2019 or 2016, consider compatibility before replacing an older formula.

Frequently Asked Questions

Is XLOOKUP exact match by default?
Yes. The default match mode is exact, so a basic formula looks for the requested value rather than the nearest value. Use the fifth argument only when you need a different matching rule.

Can XLOOKUP return a value to the left?
Yes. Select the range to search and separately select the range to return. The return range can be on either side of the lookup range, as long as corresponding rows or columns align.

Why does XLOOKUP return #N/A?
Usually, Excel cannot find an exact match. Check for spelling differences, spaces, inconsistent number or text types, and values that are absent from the lookup range. You can supply a not-found message as the fourth argument.

Does XLOOKUP work in Excel 2019?
No. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not Excel 2019 or Excel 2016. Use a compatible alternative such as INDEX/MATCH or VLOOKUP if the workbook must run in those older versions.

Can XLOOKUP return multiple columns?
Yes. Set the return array to include multiple columns, such as B2:C6. In supported versions, Excel spills the matching row’s results into adjacent cells, which must be clear.

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.