To find a product’s price by its ID in Excel, enter =XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6,"Not found") in the result cell. If E2 contains SKU-102, this example returns 18.5; the steps below show how to set up the data, understand the formula, and adapt it safely.
What XLOOKUP Does
XLOOKUP searches one range for a value and returns the corresponding value from another range. It is useful for finding prices from product codes, staff details from employee IDs, or dates from transaction numbers. Unlike older lookup approaches, the lookup column does not have to be the first column in a table, and you do not have to count a column number to tell the function what to return.
In Microsoft Excel for Microsoft 365 on Windows, XLOOKUP uses an exact match by default. That makes it a practical choice for codes and names, where returning a near match could produce a misleading result. It can also search for approximate matches, look from the bottom of a range upward, and return a chosen message when it cannot find a match.
Set Up the Example Data
Start with a small product list. Enter these headers and values in cells A1:C6:
- A1: Product ID; B1: Product; C1: Price
- Row 2: SKU-101; Ceramic mug; 12.5
- Row 3: SKU-102; Water bottle; 18.5
- Row 4: SKU-103; Notebook; 4.25
- Row 5: SKU-104; Desk lamp; 32
- Row 6: SKU-105; Tote bag; 9.75
In E1, type Lookup ID, and in E2, type SKU-102. Cell E2 is the value Excel will search for. The matching product ID is in column A, while the price to return is in column C. The product-name column between them does not prevent XLOOKUP from returning the price.
XLOOKUP Example Step by Step
- Choose where the answer should appear. Click F2, or another empty cell near your lookup value.
- Enter the formula. With the IDs in A2:A6, prices in C2:C6, and the value to find in E2, type
=XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6,"Not found"). - Press Enter. Excel searches A2:A6 for SKU-102 and returns the value from the same relative position in C2:C6. The result is 18.5.
- Check the result against the source row. SKU-102 is in row 3 and the price in C3 is 18.5, so the returned value is correct. If the price cells use currency formatting, the same numeric result may display as $18.50.
- Try another ID. Replace E2 with SKU-104. The result should change to 32. If you enter a code absent from the list, the formula displays Not found.
The dollar signs keep the source ranges fixed if you copy the formula down, while E2 remains relative and changes to E3, E4, and so on. For a single lookup, the dollar signs are optional; they matter when you plan to copy the formula to other rows.
How to Read the Formula
Read the example from left to right: =XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6,"Not found"). The first argument, E2, is the value being searched for. The second, A2:A6, is the lookup range. The third, C2:C6, is the return range. The optional fourth argument supplies the result to show when there is no match.
The lookup and return ranges must correspond: the first item in the lookup range pairs with the first item in the return range, the second with the second, and so on. Here, both ranges contain five cells. They can be in separate parts of a worksheet, and the return range can be to the left or right of the lookup range. XLOOKUP can also return a value from a row when you search horizontally across a row.
The “Not found” message is optional, but it is often clearer than Excel’s default #N/A error when a code is missing. If you leave out that argument, a missing exact match normally produces #N/A. The message is text, so put it in straight double quotation marks inside the formula.
Exact, Approximate, and Reverse Searches
For ordinary lookup tasks, the default exact match is usually right. XLOOKUP also has a match-mode argument that changes how it treats values that are not identical:
- 0: Exact match. This is the default.
- -1: Exact match, or the next smaller item.
- 1: Exact match, or the next larger item.
- 2: Wildcard match, which can use characters such as * and ? in text searches.
Approximate matching is handy for bands or thresholds, such as assigning a shipping rate based on order weight. Suppose the lower bounds are in H2:H5 in ascending order: 0, 50, 100, and 250. Put the corresponding rates in I2:I5, for example 0%, 5%, 10%, and 15%, and enter an order weight of 130 in G2. With those cells arranged as described, this formula returns 10% because 100 is the greatest lower bound not exceeding 130: =XLOOKUP(G2,H2:H5,I2:I5,"No tier",-1). Check that the threshold list is ordered appropriately when using approximate matching; an incorrect order can produce an unintended tier.
A separate search-mode argument controls direction. XLOOKUP normally searches from the first item to the last. Setting the search mode to -1 searches from last to first, which can return the latest entry when records are arranged in chronological order. For example, with IDs in J2:J5 as A01, A02, A01, A03; corresponding status values in K2:K5 as New, New, Revised, New; and A01 in M2, the formula =XLOOKUP(M2,J2:J5,K2:K5,"Not found",0,-1) returns Revised. This is useful for repeated IDs, but only if the rows are ordered so the last occurrence is the one you want.
Common XLOOKUP Problems
If the formula returns #N/A, first check whether the lookup value really exists in the lookup range. A code that looks identical can contain an extra space, or one value may be stored as text while the other is numeric. Compare the source and lookup values, and check for hidden spaces or inconsistent formatting. The optional not-found message can make a missing result easier to recognize, but it does not fix mismatched data.
If Excel displays #VALUE!, check that the lookup and return ranges have compatible shapes. For a vertical search, use vertical ranges that line up row for row; for a horizontal search, use horizontal ranges that line up column for column. If the formula returns a surprising result rather than an error, check for duplicate lookup values. An ordinary XLOOKUP returns the first matching item it encounters, so repeated IDs may need cleaning or a reverse search.
Another common issue is entering the wrong return range. If you search IDs in A2:A6 but accidentally point the return range to B2:B6, Excel will return product names rather than prices. Before relying on a lookup, test at least one value whose matching source row is easy to verify. Also confirm that the formula includes all the relevant rows; a new item added below row 6 will not be included in the fixed A2:A6 range.
For a growing list, converting the data into an Excel table can make formulas easier to maintain. Select a cell in the data and choose Insert > Table, then confirm whether the table has headers. Table references can expand as you add rows, though their exact names depend on the table and column names in your workbook. If you use fixed cell ranges instead, remember to expand them when adding records.
XLOOKUP Compared with VLOOKUP
VLOOKUP searches in the first column of a table and returns a value from a column to its right, identified by a column index number. XLOOKUP takes separate lookup and return ranges, so it can return values from either side of the search column. It also defaults to exact matching, whereas VLOOKUP requires an explicit setting for exact matching in many common uses. This can make XLOOKUP formulas easier to read and less fragile when columns are inserted.
XLOOKUP is not available in some older Excel releases, including Excel 2016 and Excel 2019. If a workbook must work in those versions, VLOOKUP or an INDEX-and-MATCH approach may be more compatible. When sharing a workbook, consider the recipient’s Excel version: an unsupported function may appear as #NAME? to someone using an older release. Excel for Microsoft 365 on Windows, Excel for Microsoft 365 for Mac, and Excel for the web support XLOOKUP; the core formula is the same across those platforms.
Frequently Asked Questions
Does XLOOKUP search for an exact match by default?
Yes. Its default match mode is exact, so you can generally omit the match-mode argument for IDs, names, and other values that should match precisely.
Can XLOOKUP return a value from a column to the left?
Yes. The lookup range and return range are specified separately, so the return range can be to the left or right of the lookup range. That is one practical difference from VLOOKUP’s usual table layout.
What does XLOOKUP return when it cannot find a value?
By default, a failed search returns #N/A. Add a not-found value, such as the text “Not found,” as the fourth argument to show a more readable result.
How do I make XLOOKUP return the last match?
Set the search mode to -1, which searches from the last item toward the first. If you do this with repeated records, make sure the row order reflects what “last” should mean, such as the most recent record appearing at the bottom.
Why does my version of Excel not recognize XLOOKUP?
XLOOKUP is included in Microsoft 365 and Excel 2021 or later, but not in Excel 2016 or Excel 2019. If the function is unavailable in the version opening the file, use a compatible lookup method or open the workbook in a supported version.
Related
- Data Validation in Excel: Steps for Reliable Entries
- Excel Data Validation Shortcut: Windows and Mac
- Data Validation Plan in Excel: Build Reliable Checks
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Volume of a Right Cylinder: Formula, Steps and ExamplesThe volume of a right cylinder is V = πr²h: multiply pi by the radius squared, then by…
- Find Unique Names in Excel: 3 Reliable MethodsTo find a list of distinct names in Excel, enter =UNIQUE(A2:A100) in an empty cell if you use…
- Excel SUBTOTAL Formula Examples for Filtering and TotalsWith sales values in B2:B6, use =SUBTOTAL(9,B2:B6) to add them; for the example values 120, 80, 150, 70,…
- Excel INDEX Function Examples for Everyday LookupsExcel’s INDEX function returns the value at a specified row and column within a range. For example, with…
- COUNTIF in Excel: Count Cells That Contain NumbersYes—COUNTIF can count cells that meet a numeric condition, and it can find text containing a particular digit.…
- Excel Date Minus 1 Month? Formula and Date RulesTo subtract one calendar month from an Excel date, use =EDATE(A1,-1), where A1 contains a real Excel date.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.