XLOOKUP Example Table: Formulas, Results, and Tips in Excel

With product IDs in A2:A6, prices in C2:C6, and the ID to find in E2, use =XLOOKUP(E2,A2:A6,C2:C6,"Not found"). In the example below, entering 103 in E2 returns 179.00. XLOOKUP searches one range and returns the corresponding value from another, making it useful for finding prices, names, dates, or other related information in an Excel worksheet.

A Simple XLOOKUP Example Table

Here is a small product list. The first column contains unique product IDs; the other columns contain details associated with each ID.

Product ID	Product	Unit price
101	Keyboard	29.99
102	Mouse	18.50
103	Monitor	179.00
104	Webcam	49.00
105	Headset	64.00

Assume the table occupies A1:C6, with headers in row 1, and the value to search for is entered in E2. The formula in F2 is:

With IDs in A2:A6, prices in C2:C6, and the search ID in E2, enter =XLOOKUP(E2,A2:A6,C2:C6,"Not found") in F2. If E2 contains 103, F2 returns 179.00, the price on the row where the ID matches.

The example illustrates the basic relationship: XLOOKUP looks for a value in one range, then returns the value from the corresponding position in another range. It does not need the return column to be to the right of the search column. You can search in column C and return a value from column A, for example.

How XLOOKUP Works

The function’s full syntax is XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode]). The first three arguments are required. The remaining arguments are optional and let you specify what to return when there is no match, how to match, and which direction to search.

lookup_value: The item to find, such as a product ID in E2.
lookup_array: The one row or one column Excel searches, such as A2:A6.
return_array: The corresponding row or column from which Excel returns a result, such as C2:C6.
if_not_found: Optional text or a value to show if no match exists. Without it, XLOOKUP returns #N/A.
match_mode: Optional setting for exact, approximate, or wildcard matching.
search_mode: Optional setting for search direction or a binary search on sorted data.

By default, XLOOKUP uses an exact match and searches from the first item to the last. For a small inventory list, that means an ID of 103 must match 103; Excel does not silently choose a nearby ID. This default differs from the usual VLOOKUP behavior when its final argument is omitted, which can use an approximate match.

The search and return ranges need compatible dimensions. For a vertical lookup, use one column for lookup_array and a corresponding column, or a set of columns, for return_array. If one range has five rows and the other has four, the positions cannot line up properly and Excel reports an error.

Build and Check the Example in Excel

In Microsoft Excel for Microsoft 365 on Windows, enter the product information in A1:C6, including the headers. Enter a product ID such as 103 in E2, then put the lookup formula in F2. Select F2 and use Home > Number Format to choose a suitable display, such as Currency, if you want the result to show as a price. Number formatting changes how the value appears, not the underlying lookup.

Keep the lookup IDs in a consistent format. Do not mix numbers stored as numbers with IDs stored as text unless you account for that difference.
Put the value to search for in a separate input cell, such as E2. This makes the formula reusable: change E2 to another ID to return another price.
Choose the column that contains those IDs as lookup_array and the desired result column as return_array.
Add a clear not-found message if it is more useful to a reader than Excel’s #N/A error.
Test the formula with an ID that exists and one that does not. Confirm that the returned value belongs to the same row as the matching ID.

You can also convert the product range into an Excel Table. Select a cell in the range and choose Insert > Table, then confirm that the table has headers. On the Table Design tab, give the table a meaningful name such as Products. Structured references make formulas easier to read and can expand as rows are added to the table.

With the ID column named ProductID, the price column named UnitPrice, and the lookup ID in E2, enter =XLOOKUP(E2,Products[ProductID],Products[UnitPrice],"Not found"). This returns the price associated with the matching ID. The table name and column names in a structured-reference formula must match the names in your workbook.

Return More Than One Result Column

XLOOKUP can return a range that contains multiple columns. This is handy when a search should display several details from the same record, such as a product name and its price. With product IDs in A2:A6, product names and prices in B2:C6, and the requested ID in E2, enter =XLOOKUP(E2,A2:A6,B2:C6,"Not found") in G2. If E2 contains 103, the result spills into G2:H2 as Monitor and 179.00.

Spilling means Excel places the results in neighboring cells automatically. Keep those destination cells empty; a value already in the spill area can cause a #SPILL! error. The result also depends on using a version of Excel that supports dynamic arrays. In current Microsoft 365 Excel, a multi-column return range can spill across cells without copying the formula horizontally.

For a single result, keep the return_array to one column or row. A two-column return range is not necessary if the task is only to retrieve a price, and a single output is often easier to use in a separate calculation.

Exact, Approximate, and Wildcard Matches

Exact matching is the normal choice for IDs, employee numbers, invoice references, and product codes. It is XLOOKUP’s default, so the basic formula already requests an exact match. If the lookup value is missing, use the fourth argument to return a readable message such as Not found rather than #N/A.

Approximate matching is useful for threshold tables, such as assigning a grade or rate based on the highest threshold that does not exceed a score. Suppose A2:A5 contains the ascending thresholds 0, 50, 70, and 90, while B2:B5 contains the labels Basic, Standard, Plus, and Premium. With the score in E2, use match_mode -1 to find an exact threshold or the next smaller one.

With thresholds in A2:A5, labels in B2:B5, and the score in E2, enter =XLOOKUP(E2,A2:A5,B2:B5,"No tier",-1). If E2 is 72, the result is Plus because 70 is the greatest listed threshold below 72. This assumes the thresholds and labels are aligned row by row.

For wildcard matching, set match_mode to 2. For example, an asterisk can stand for any number of characters, and a question mark can stand for one character. With product names in B2:B6, IDs in A2:A6, and the text key* in E2, use =XLOOKUP(E2,B2:B6,A2:A6,"Not found",2). This finds the first name matching that pattern, such as Keyboard, and returns its ID. Wildcards are for text searches; use exact matching for a literal ID or other value that should not be treated as a pattern.

Search Direction and Duplicate Values

When a lookup value appears more than once, the default search returns the first match in the lookup range. That might be appropriate for a customer list, but not when you need the most recent entry in a transaction log. Set search_mode to -1 to search from the last item to the first.

With IDs in A2:A20, dates or transaction details in C2:C20, and the ID to find in E2, enter =XLOOKUP(E2,A2:A20,C2:C20,"Not found",0,-1). This returns the result associated with the last matching ID in the range. It does not compare dates or decide which record is newest; it relies on the rows being arranged so that the desired record is last.

XLOOKUP also offers binary search modes: 2 for an ascending-sorted lookup array and -2 for a descending-sorted one. Those modes can be useful with large, correctly sorted ranges, but the data must actually be sorted in the required order. An unsorted range can produce an incorrect result, so the default linear search is safer for ordinary lists unless sorting is controlled.

Common XLOOKUP Problems

#N/A: No exact match was found, or the lookup value differs from the stored value. Check spelling, extra spaces, number-versus-text formatting, and whether the value is present. A custom if_not_found message can improve presentation, but it should not conceal a data problem during troubleshooting.
#VALUE!: The lookup and return ranges may have incompatible shapes. Check that the rows or columns correspond and cover the intended records.
#SPILL!: A multi-cell result cannot fill its destination because one or more cells in the spill area are occupied. Clear those cells or move the formula.
Wrong match: Duplicate values return the first occurrence by default. Use reverse search only if the last occurrence is the one you need, and verify the row order.
Wrong approximate result: Confirm the match mode and, for binary search modes, the required sort order. Approximate lookup is not a substitute for an exact lookup when codes must match precisely.
#NAME?: The Excel version may not recognize XLOOKUP, or the function name may have been mistyped. Older perpetual versions such as Excel 2019 and Excel 2016 do not include XLOOKUP.

A blank returned cell is not always the same as a missing match. If a matching row’s return cell is empty, the result can appear blank; if no match exists and no if_not_found argument is supplied, the result is #N/A. Decide whether a blank is a valid answer or a sign that the source data needs attention.

XLOOKUP Compared with VLOOKUP and INDEX/MATCH

VLOOKUP searches the first column of a table and returns a value from a column to its right, selected by a column number. This makes it less flexible when the desired result is to the left of the search column or when columns are inserted and the numbered return position changes. XLOOKUP takes separate lookup and return ranges, so it can return from either side and does not require a column index number.

INDEX/MATCH can also perform flexible lookups, including searches to the left, and remains useful in workbooks that must support older Excel versions. It generally requires combining two functions, while a basic XLOOKUP uses one. The best option depends partly on who will open the workbook: a formula using XLOOKUP may show as an unsupported-function error in older Excel releases.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. It is also available in current Excel for the web. The function is not included in Excel 2019 or Excel 2016. If a file will be shared with people using older desktop versions, check compatibility before building essential calculations around XLOOKUP; consider INDEX/MATCH or another formula supported by their software.

Frequently Asked Questions

What is a simple XLOOKUP formula?
With the value to find in E2, lookup values in A2:A6, and results in C2:C6, use =XLOOKUP(E2,A2:A6,C2:C6,"Not found"). It returns the result on the row where E2 matches a value in A2:A6.

Does XLOOKUP return an exact match by default?
Yes. Its default match mode is exact, so the lookup value must match an item in the lookup array. If no match exists, XLOOKUP returns #N/A unless you provide an if_not_found value.

Can XLOOKUP look to the left?
Yes. The lookup array and return array are separate arguments, so the return range can be located to the left or right of the lookup range. Choose ranges whose rows or columns line up.

How do I make XLOOKUP return the last match?
Set the sixth argument, search_mode, to -1. This searches from the end of the lookup array toward the beginning. The last match means the last one in the current range order, not necessarily the newest by date.

Why is XLOOKUP showing #N/A?
Usually, Excel cannot find an exact matching value. Check that the value exists and that text, numbers, spacing, and punctuation match. If a missing result is expected, provide an if_not_found message; if it is unexpected, investigate the source data first.

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.