Excel XLOOKUP Formula Examples for Everyday Lookups

Excel’s XLOOKUP function finds a value in one range and returns a related value from another. For example, with product IDs in A2:A4 and prices in B2:B4, use =XLOOKUP("P-203",A2:A4,B2:B4,"Not found") to return the price for P-203, or the message Not found if the ID is absent.

What XLOOKUP Does and When to Use It

XLOOKUP is a flexible lookup function for matching records across rows or columns. It can look from left to right or right to left, return a value from a separate range, and handle missing matches without wrapping the formula in another function. It is often a clear choice for tasks such as finding a product price from an item code, an employee’s department from an ID, or a grade band from a score.

Unlike older VLOOKUP formulas, XLOOKUP does not require the lookup column to be the first column in a table, and it does not ask you to count a return-column number. The lookup range and return range are specified separately. XLOOKUP also uses exact matching by default, which is usually what you want when searching for a unique identifier.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. It is not available in Excel 2019 or Excel 2016, so a workbook using it may show #NAME? in those versions. Current Excel for Mac and Excel for the web also support XLOOKUP.

How the XLOOKUP Formula Works

The function’s argument order is XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode]). The first three arguments are the essential ones: the value to find, the range to search, and the range containing the answer. The optional arguments let you control missing results, approximate or wildcard matching, and search direction.

lookup_value: The value or cell reference you want to find.
lookup_array: The single row or column Excel searches.
return_array: The row, column, or compatible array containing the result.
if_not_found: Optional text or value to return if there is no match.
match_mode: Optional setting for exact, approximate, or wildcard matching.
search_mode: Optional setting for search direction or a binary search.

By default, XLOOKUP searches from the first item to the last and looks for an exact match. The lookup and return ranges need to correspond in size and orientation: a vertical lookup range normally pairs with a vertical return range. If a value appears more than once, the default search returns the first matching row.

Basic Exact-Match Example

Suppose A2:A4 contains product IDs P-104, P-203, and P-310, while B2:B4 contains their prices: 7.25, 18.50, and 4.00. To find the price for P-203, enter this formula in another cell:

With product IDs in A2:A4 and prices in B2:B4, this formula returns 18.50:

=XLOOKUP("P-203",A2:A4,B2:B4,"Not found")

The fourth argument supplies a readable fallback if the code does not exist. If you omit it, an unsuccessful exact search returns #N/A. A cell reference is convenient when the item to search for changes: put the requested ID in E2 and replace the quoted ID with E2. That way, you can change the lookup by editing a cell rather than rewriting the formula.

Return a Helpful Message When There Is No Match

For reports shared with other people, a plain missing-value message can be easier to understand than an error. Use a message that distinguishes a genuine missing record from a blank result. In the same product table, searching for P-999 should return Not found.

With product IDs in A2:A4, prices in B2:B4, and the requested ID in E2, this formula returns Not found when E2 contains P-999:

=XLOOKUP(E2,A2:A4,B2:B4,"Not found")

The optional message only applies when no match is found; it does not hide every kind of formula problem. For example, an invalid range reference can still produce a different error. If a blank cell is a suitable result instead, use an empty text value for the fourth argument: "". Remember that a blank-looking result can make missing data less obvious, so choose it intentionally.

Look to the Left or Return Several Columns

XLOOKUP is not restricted to returning values positioned to the right of the lookup column. If employee names are in A2:A4 and employee IDs are in B2:B4, you can search the IDs and return the names by putting the ID range before the name range in the formula.

With names in A2:A4 and IDs in B2:B4, this formula returns Carson for ID E-17:

=XLOOKUP("E-17",B2:B4,A2:A4,"Not found")

XLOOKUP can also return several neighboring columns at once. Suppose product IDs are in A2:A4 and product names, categories, and prices are in B2:D4. If P-203 is in E2, the formula below returns the corresponding row from B:D: Notebook, Stationery, and 18.50. In a current Excel version, the results spill into adjacent cells automatically, so leave those cells clear.

With IDs in A2:A4 and product details in B2:D4, this formula returns the three details for the ID in E2:

=XLOOKUP(E2,A2:A4,B2:D4,"Not found")

If the cells needed for the spilled result are occupied, Excel displays #SPILL!. Clear the obstructing cells or place the formula somewhere with enough empty space. Spilled-array behavior is available in Microsoft 365 and Excel 2021 or later.

Approximate Match for Tiers and Thresholds

Approximate matching is useful when a value belongs to a band rather than matching a specific item. For instance, a score may map to a level based on the greatest threshold that does not exceed it. Put thresholds in ascending order in A2:A5: 0, 60, 70, and 80. Put the corresponding labels in B2:B5: Entry, Bronze, Silver, and Gold. If E2 contains 87, the formula returns Gold because 80 is the nearest threshold below 87.

With ascending score thresholds in A2:A5, labels in B2:B5, and the score in E2, this formula returns Gold for 87:

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

The match mode -1 means exact match, or the next smaller item if there is no exact match. Match mode 1 instead means exact match, or the next larger item. Do not confuse these settings with search mode, which is the sixth argument. For threshold tables, keep the values ordered in the direction required by your logic and verify results at boundaries such as 70 and 80. An exact threshold match returns that threshold’s own label.

Wildcard Matching and the Last Matching Record

When searching text that is only partly known, use wildcard matching. The wildcard characters are * for any number of characters and ? for one character. A tilde can escape a wildcard when you need to search for the character itself. Wildcard matching requires match mode 2.

With names in A2:A4, departments in B2:B4, and the text *son in E2, this formula returns the department for the first name ending in son:

=XLOOKUP(E2,A2:A4,B2:B4,"Not found",2)

If A2:A4 contains Carson, Miller, and Wilson, and B2:B4 contains Support, Sales, and Finance, this formula returns Support because Carson is the first match. To get a later match when the same key appears multiple times, change the search direction. For example, if A2:A5 contains repeated SKU values and B2:B5 contains prices, search from bottom to top to return the latest listed price.

With SKUs in A2:A5, prices in B2:B5, and the requested SKU in E2, this formula returns 13.25 if the last matching row has that price:

=XLOOKUP(E2,A2:A5,B2:B5,"Not found",0,-1)

Here, match mode 0 explicitly requests an exact match, and search mode -1 searches from the last item toward the first. This returns the last match in the range, not necessarily the newest record by date. Make sure the rows are arranged so that the last occurrence represents the result you intend.

Use Multiple Criteria in One Lookup

You can combine conditions when a single field is not unique, such as finding a quantity for both a region and an item. If A2:A5 contains regions, B2:B5 contains item names, and C2:C5 contains quantities, put the requested region in E2 and the requested item in F2. The multiplication of the two comparisons acts as a combined test: only a row meeting both conditions produces a 1.

With regions in A2:A5, items in B2:B5, quantities in C2:C5, the region in E2, and the item in F2, this formula returns 24 if the matching East/Pens row has quantity 24:

=XLOOKUP(1,(A2:A5=E2)*(B2:B5=F2),C2:C5,"Not found")

All the compared ranges must cover the same rows. If no row meets both conditions, the fallback text appears. If more than one row meets them, the normal first-to-last search returns the first qualifying row; add a reverse search mode if the last qualifying row is the one you need. For a more maintainable workbook, label the criteria cells and keep the source data in a consistent table.

Common XLOOKUP Problems and How to Fix Them

When a formula does not return the expected result, check the data and arguments before changing the formula repeatedly. Small differences in text, range size, or matching mode can explain many lookup issues.

#NAME?: The Excel version may not support XLOOKUP, or the function name may be misspelled. Use Microsoft 365 or Excel 2021 and later.
#N/A: No exact match was found and no not-found result was supplied. Check for extra spaces, different punctuation, or text-versus-number mismatches.
Unexpected first result: The lookup value occurs more than once. Use search mode -1 to return the last match, or improve the source data so the key is unique.
#VALUE! or an unexpected result: Confirm that lookup and return arrays have compatible dimensions and that the selected match mode suits the data.
#SPILL!: A cell is blocking a multi-cell result. Clear the cells where the returned array needs to appear.

Also check whether numeric-looking codes are stored consistently. The number 123 and the text value “123” may not behave as the same key. If the source data includes leading or trailing spaces, clean or standardize it rather than relying on increasingly complicated lookup formulas. For a formula that should match text exactly, remember that XLOOKUP is not case-sensitive by default.

XLOOKUP Compared with VLOOKUP and INDEX/MATCH

VLOOKUP remains familiar and can be useful in older workbooks, but it searches the first column of its table and returns a column to its right using a column index. Inserting or moving columns can make that index harder to maintain. XLOOKUP instead names the lookup and return ranges directly, supports lookups in either direction, and defaults to exact matching.

INDEX and MATCH can also look in either direction and are supported in older Excel versions. They can be useful when a workbook must work in Excel 2016 or 2019, where XLOOKUP is unavailable. However, they generally require a more involved formula. If compatibility with older versions is not a requirement, XLOOKUP is often easier to read. Before sharing a file, consider the Excel versions its recipients use; a formula that works on your computer may not work in an older installation.

Frequently Asked Questions

Does XLOOKUP return the first or last match?
By default, it searches from the first item to the last and returns the first match. Set search mode to -1, as the sixth argument, to search from the last item upward and return the last match.

How do I make XLOOKUP return blank if there is no match?
Use an empty text value as the fourth argument, for example =XLOOKUP(E2,A2:A10,B2:B10,""). With the lookup value in E2, lookup range A2:A10, and return range B2:B10, this returns a blank-looking result when there is no match. Use this only if hiding the absence of a match is appropriate.

Can XLOOKUP search from right to left?
Yes. Put the range containing the search values in the second argument and the range containing the desired answer in the third. The return range can be to the left or right of the lookup range.

Why does XLOOKUP show #N/A when the value looks the same?
The underlying values may differ even if they look alike: one might be text and the other a number, or one may contain an extra space or different character. Check the source values and standardize the data. You can also add a not-found message as the fourth argument while investigating.

Is XLOOKUP available in Excel 2019?
No. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not Excel 2019 or 2016. For files that must work in those older versions, use a compatible alternative such as INDEX/MATCH or VLOOKUP.

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.