Microsoft 365 XLOOKUP Examples for Everyday Excel Tasks

In Microsoft 365, XLOOKUP finds a value in one range and returns the corresponding value from another: use =XLOOKUP(lookup_value,lookup_array,return_array,"Not found"). For example, look up a product code in column A and return its price from column B; XLOOKUP can also search in reverse, find approximate matches, and return multiple columns.

What XLOOKUP does and when to use it

XLOOKUP is a lookup function in Excel for Microsoft 365. It is also available in Excel 2021 and later. It replaces many common uses of VLOOKUP and HLOOKUP with a formula that explicitly identifies the range to search and the range to return. The lookup and return ranges do not have to be adjacent, and the return range can be to the left of the lookup range.

The function’s arguments are XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode]). The first three arguments are required. The optional fourth argument lets you choose a message for a missing match. By default, XLOOKUP looks for an exact match and searches from the first item to the last.

Use it for tasks such as matching an employee ID to a department, finding an order’s status, or retrieving a price from a product list. It is not necessary to sort data for the default exact-match search. Excel for the web supports XLOOKUP too; the formulas below work there as well. On Mac, the function works the same way, although keyboard shortcuts and some interface details differ.

Example: return a value for an exact match

Suppose product codes are in A2:A100, product names are in B2:B100, and the code to find is entered in E2. The following formula returns the matching name, or a clear message if E2 is not in the list:

With product codes in A2:A100, names in B2:B100, and the code to look up in E2:

=XLOOKUP(E2,A2:A100,B2:B100,"Product not found")

If E2 contains P-104 and that code is in A17, the result is the name from B17. The fourth argument controls what appears when there is no match. If you omit it, XLOOKUP returns the Excel error #N/A instead. A message such as “Product not found” can make a sheet easier for other people to interpret.

The ranges should correspond item by item: A2 is paired with B2, A3 with B3, and so on. They can be different shapes for horizontal or vertical lookups, but they must line up in the direction of the search. Keep the ranges the same length to avoid confusing results.

Example: look to the left or return a different column

Unlike VLOOKUP, XLOOKUP does not require the search column to be the leftmost column of a table. It can search in column C and return a value from column A, which is useful when a workbook’s existing layout cannot be rearranged.

With employee IDs in C2:C50, employee names in A2:A50, and the ID to find in E2:

=XLOOKUP(E2,C2:C50,A2:A50,"ID not found")

If E2 contains the ID from C12, the formula returns the name in A12. The search range and return range are separate arguments, so their left-to-right order on the worksheet does not matter. This also makes a lookup more resilient than a VLOOKUP formula that uses a column number: inserting a column into the table can change what that number refers to.

For a horizontal lookup, put the lookup values in a row and the corresponding results in another row. With month names in B1:M1, revenue in B2:M2, and the month to find in E4, use =XLOOKUP(E4,B1:M1,B2:M2,"Month not found"). It returns the value from the matching column in row 2.

Example: return several columns at once

XLOOKUP can return a multi-column range. In a dynamic-array-enabled version of Excel, the results spill into neighboring empty cells, so you do not need a separate lookup formula for each field.

With product IDs in A2:A20, product names and prices in B2:C20, and the ID to find in E2:

=XLOOKUP(E2,A2:A20,B2:C20,"Not found")

If the matching product is on row 8, the name from B8 appears in the formula cell and the price from C8 spills into the cell to its right. The destination cells need to be empty; otherwise Excel may show #SPILL!. Clear the blocking cells or move the formula. If you only want the price, set the return array to C2:C20 rather than B2:C20.

Example: find the latest matching record

When a lookup value occurs more than once, the default search returns the first match from the top. To retrieve the last matching entry instead, set the sixth argument, search_mode, to -1. This is useful for finding the most recent row in a log when newer records are appended below older ones.

With order IDs in A2:A200, status values in B2:B200, and the order ID to check in E2:

=XLOOKUP(E2,A2:A200,B2:B200,"Order not found",0,-1)

If the order ID appears on rows 12, 70, and 155, the formula returns the status from B155. The 0 explicitly requests an exact match; -1 searches from the last item to the first. This returns the last matching row, not necessarily the latest date. If records are not ordered chronologically, use a date-aware method or sort the data appropriately before treating the last row as the newest record.

Example: match part of a text value

For a wildcard search, set match_mode to 2. The asterisk represents any number of characters, while the question mark represents one character. For instance, a search term placed between asterisks can find text containing that term anywhere in a cell.

With descriptions in A2:A50, category labels in B2:B50, and a search word in E2:

=XLOOKUP("*"&E2&"*",A2:A50,B2:B50,"No description found",2)

If E2 contains cable, the formula can match a description such as “USB cable, two metres” and return the corresponding category. XLOOKUP returns the first matching description in the default search direction. Wildcards are not active in the default exact-match mode; the final 2 is what enables wildcard matching. If the search word itself includes a wildcard character that should be treated literally, this pattern needs additional handling rather than assuming it will be interpreted as ordinary text.

Example: choose the nearest lower threshold

Approximate matching is useful for assigning a band or rate from threshold values. For match_mode -1, XLOOKUP finds an exact match or, if there is none, the next smaller value. This is often appropriate for score bands, provided the thresholds represent the lower boundary of each band.

With minimum scores in A2:A5 (0, 60, 70, and 80), labels in B2:B5 (“Needs review,” “Pass,” “Good,” and “Excellent”), and a score in E2:

=XLOOKUP(E2,A2:A5,B2:B5,"Below range",-1)

If E2 is 76, the result is “Good,” because 70 is the next smaller threshold. If E2 is 80, it returns “Excellent” as an exact match. Check the lowest threshold and the meaning of values below it; in this example, a score below 0 receives “Below range.” For approximate lookups, thresholds should be arranged in an order that makes the intended bands clear. Do not confuse match_mode -1 with search_mode -1: the former controls approximate matching, while the latter reverses the direction of the search.

Example: match on two conditions

You can combine two fields into a single lookup key when a value is only unique as a pair, such as a product within a region. The lookup and return arrays in this example are the same length, and the ampersand joins each row’s two fields into a combined key.

With product names in A2:A100, regions in B2:B100, quantities in C2:C100, the product to find in E2, and the region in F2:

=XLOOKUP(E2&F2,A2:A100&B2:B100,C2:C100,"No matching record")

If E2 is “Notebook” and F2 is “West,” the formula returns the quantity from the first row where both fields match. Be aware that simple concatenation can be ambiguous: “AB” plus “C” creates the same combined text as “A” plus “BC.” If the source values could create such collisions, use a consistent delimiter in both the lookup key and the array expressions, or add a helper column with an unambiguous key. Duplicate pairs still return the first match unless you change the search direction.

Common problems and how to fix them

Most XLOOKUP issues come from mismatched data or ranges rather than a complicated formula. Check these points before rebuilding a working lookup:

#N/A: The lookup value may not exist, or it may differ from the stored value because of extra spaces, inconsistent punctuation, or a number stored as text. The if_not_found argument can display a helpful message, but it does not make unequal values match.
Wrong result with duplicates: The default is the first match. Use reverse search when you need the last occurrence, and verify that the row order actually represents recency if that is the goal.
#SPILL!: A multi-cell return needs clear cells for its results. Remove anything blocking the spill range or return just the single column you need.
Unexpected approximate result: Review the match_mode argument. Exact matching is the default; -1 and 1 request neighboring-value matches, and 2 enables wildcards.
Formula not recognized: XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in older perpetual releases such as Excel 2019. A workbook containing it may show #NAME? in an unsupported version.

Excel formulas shown here use commas as argument separators, as required for many regional settings. Some installations use semicolons instead, depending on the operating system’s regional settings. If Excel rejects an otherwise correct formula, check the separator convention for that installation. A lookup formula does not change the source data; it only calculates and displays a result.

XLOOKUP compared with VLOOKUP and INDEX/MATCH

VLOOKUP remains familiar and can be useful when a workbook must support older Excel versions. However, it searches the first column of a table and returns a column to its right using a numeric column index. That structure makes leftward lookups awkward and can make formulas harder to maintain after columns are inserted or rearranged. XLOOKUP identifies the search and return ranges directly and defaults to exact matching.

INDEX/MATCH is another established approach. It can look left or right and is supported in older Excel versions, but typically requires combining two functions. If a workbook must be opened in Excel 2019 or an earlier version, choose a compatible alternative such as INDEX/MATCH or VLOOKUP rather than assuming XLOOKUP will calculate for every recipient. For files used only with Microsoft 365 or Excel 2021 and later, XLOOKUP is often easier to read for ordinary lookups.

Frequently Asked Questions

Does XLOOKUP work in every version of Excel?
XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including Excel for the web. It is not available in Excel 2019 and earlier, so use a compatible lookup method if recipients may use those versions.

Does XLOOKUP need the lookup column sorted?
No, not for its default exact-match search. Sorting is important for some binary-search modes, but those modes are optional and should only be used when the data is sorted as required. The examples here use the standard search direction unless noted.

How do I return a blank when no match is found?
Set the fourth argument to an empty text string: =XLOOKUP(E2,A2:A100,B2:B100,""). This displays a blank-looking result when there is no match, though the cell contains a formula rather than being truly empty.

Can XLOOKUP return more than one result?
It can return multiple columns from the row it finds, and those values spill into adjacent cells when the workbook supports dynamic arrays. It does not by itself return every row that meets a condition; for a list of all matching rows, a filtering function such as FILTER may be more suitable.

Why does XLOOKUP return the wrong duplicate?
By default, it returns the first match it encounters. Set search_mode to -1 to search from the bottom upward and return the last match. Confirm that the order of the rows corresponds to what “last” means in your data.

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.