If Orders!A2 contains a product code, and Products!A2:A100 contains product codes with prices in Products!C2:C100, enter =XLOOKUP(A2,'Products'!$A$2:$A$100,'Products'!$C$2:$C$100,"Not found") on the Orders sheet. If A2 is P-104 and that product’s price is 24.5, the formula returns 24.5; format the result as currency if you want to display it as $24.50.
What this two-sheet lookup does
XLOOKUP searches one range for a value and returns the corresponding value from another range. In this example, the formula takes the product code from an order, finds that code in a product list on a separate worksheet, and brings the associated price back to the order sheet. The lookup and return ranges can be on different sheets in the same workbook.
The formula’s parts are:
A2is the value to find: the product code on the current sheet.'Products'!$A$2:$A$100is the lookup range on the Products sheet.'Products'!$C$2:$C$100is the range containing the prices to return."Not found"is the optional message to show if the code is missing.
By default, XLOOKUP looks for an exact match and searches from the first item to the last. That makes it a useful fit for common tasks such as matching order IDs, employee numbers, product codes, or customer names across worksheets.
Set up the two sheets
For a predictable result, use one sheet for the records that need information and another sheet as the source list. For example, the Orders sheet might contain a product code in column A and an empty price column in C. The Products sheet could have product codes in column A, descriptions in B, and prices in C.
Keep the lookup values consistent. If Orders contains P-104, the source list needs the same code, not a visually similar value with an extra space or different punctuation. A lookup range should contain one value per row, and its return range must cover the corresponding rows. The two ranges can be in different columns, but they should have the same number of rows.
To enter the formula, click the destination cell on Orders, type or paste it into the formula bar or cell, and press Enter. You can then copy it down the column. The dollar signs in the source ranges lock those references when you fill the formula down, while A2 changes to A3, A4, and so on.
Build and copy the formula
Assume Orders has product codes in A2:A50 and you want prices in C2:C50. Products has codes in A2:A100 and prices in C2:C100. In Orders!C2, use the formula below. If Orders!A2 is P-104 and Products!A7 is P-104 with 24.5 in Products!C7, the result is 24.5.
=XLOOKUP(A2,'Products'!$A$2:$A$100,'Products'!$C$2:$C$100,"Not found")
After entering it in C2, use the fill handle at the lower-right corner of the cell to copy it down, or copy and paste into the remaining cells. The fixed source ranges continue pointing to Products!A2:A100 and Products!C2:C100; the unanchored lookup reference changes for each order row. If you prefer, select C2, copy it, select the destination cells below, and paste.
Sheet names that contain spaces need single quotation marks around the name, as in 'Product List'!$A$2:$A$100. Excel may add these quotation marks automatically when you build a reference by clicking a cell or range on another sheet. A sheet name without spaces can also be quoted; the quotation marks are valid either way.
Return a description or several fields
The return range determines what XLOOKUP brings back. To return a description instead of a price, keep the same lookup range but point the return range at the description column. Assume Orders!A2 contains P-104, Products!A2:A100 contains codes, and Products!B2:B100 contains descriptions. If the matching description in Products!B7 is Travel mug, this formula returns Travel mug:
=XLOOKUP(A2,'Products'!$A$2:$A$100,'Products'!$B$2:$B$100,"Not found")
XLOOKUP can also return multiple adjacent columns in versions that support dynamic arrays. With Orders!A2 containing P-104, Products!A2:A100 containing codes, and the corresponding descriptions and prices in Products!B2:C100, enter the formula below in an empty cell such as Orders!F2. If the matching row has Travel mug in column B and 24.5 in column C, the results spill into F2 and G2 as Travel mug and 24.5. Leave the destination cell to the right clear so the results have room to spill.
=XLOOKUP(A2,'Products'!$A$2:$A$100,'Products'!$B$2:$C$100,"Not found")
Handle missing matches and duplicate codes
The fourth XLOOKUP argument, called if_not_found, lets you choose what appears when there is no match. In the examples above, it displays Not found rather than an error. You could use another short message, or return an empty string with "" if you want the cell to look blank. An empty display can be convenient, but it can also hide incomplete source data, so choose a message that suits the workbook.
Assume Orders!A2 contains P-999, while Products!A2:A100 contains no P-999 and Products!C2:C100 contains prices. This formula returns Check code because the value is absent from the lookup range:
=XLOOKUP(A2,'Products'!$A$2:$A$100,'Products'!$C$2:$C$100,"Check code")
If you omit the fourth argument and a value is not found, XLOOKUP returns the #N/A error. That error is useful when you want missing records to be obvious, while a custom message can make a report easier for other readers to interpret.
If a lookup code appears more than once, XLOOKUP normally returns the first matching row it encounters. It does not combine duplicate records or choose the newest one automatically. Clean up duplicates or define a reliable sorting rule if the first occurrence is not the correct record. You can also set the search direction to look from the end, but that is only useful when the data order makes the last match meaningful.
Common problems and how to fix them
- The formula returns Not found or #N/A. Check for extra spaces, inconsistent punctuation, and numbers stored as text in one sheet but as numbers in the other. Confirm that the formula points to the correct source sheet and includes the rows where the record appears.
- The formula returns the wrong field. Verify the return range. A lookup in column A and a return range in column C returns the value from the corresponding row in C; it does not infer which column you intended.
- Results change when you copy the formula. Lock the source ranges with dollar signs, as in
$A$2:$A$100. Leave the current-row lookup cell relative so it updates as you fill down. - The formula shows as text. Check whether the cell is formatted as Text. Change its format to General, then re-enter the formula. Also check that the entry begins with an equals sign and that Show Formulas is not enabled.
- You see #SPILL! with a multi-column return. Clear cells in the spill area, including cells that look blank but contain spaces or formulas. The formula needs an unobstructed destination for all returned columns.
For especially large workbooks, avoid searching entire columns unless there is a good reason. Bounded ranges can make the intended data area clearer and avoid including unrelated content. If the source list grows regularly, consider using an Excel table so new records can be included more conveniently; check the table’s column references carefully when constructing the formula.
Exact match, approximate match, and search direction
XLOOKUP uses exact matching by default. This is generally what you want for IDs and codes: P-104 should match P-104, not a nearby code. The optional fifth argument, match_mode, can change the matching behavior. For example, approximate matching can be useful for a sorted threshold table, such as assigning a rate based on a score. In that case, sort and design the threshold table deliberately, because an approximate result is not the same as finding an identical value.
The optional sixth argument, search_mode, controls the direction or method of the search. The default searches from the first item downward. A reverse search can return the last matching record in a list, which may suit a chronologically ordered log. It still does not determine whether that last record is correct; the source order must represent the rule you intend to apply.
XLOOKUP availability and alternatives
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 the function may show #NAME? when opened in those versions. The formula approach is broadly the same in Excel for Windows, Excel for Mac, and Excel for the web, although ribbon appearance and some editing details can differ.
For older Excel versions, a common alternative is INDEX with MATCH, which can search in one column and return a value from another. VLOOKUP is another option when the lookup column is the leftmost column of its table and the return column is to its right. Compared with those older approaches, XLOOKUP lets you specify separate lookup and return ranges directly and can return values from either side of the lookup column. If a workbook must be used by people on older Excel releases, choose a compatible formula or confirm that all users can open it in a version that supports XLOOKUP.
Frequently Asked Questions
Can XLOOKUP search a different sheet in the same workbook?
Yes. Put the other sheet’s name and an exclamation mark before the lookup and return ranges, such as 'Products'!$A$2:$A$100. The lookup value can remain on the current sheet.
Do the two sheets need to be in the same workbook?
The examples here use two worksheets in one workbook. XLOOKUP can also reference another workbook, but external links depend on that workbook’s location and availability. A broken or changed file path can make an external reference harder to maintain.
Does XLOOKUP require the source data to be sorted?
No, not for its default exact-match search. Sorting is relevant when you deliberately use an approximate match or want a reverse search to represent a meaningful “last” record.
Why does XLOOKUP return #N/A when the value looks identical?
The cells may contain hidden spaces, different characters, or values stored in different formats. Compare the source and lookup values, and check for leading or trailing spaces. Also confirm the lookup range actually includes the source row.
Can I return a value from a column to the left of the lookup column?
Yes. XLOOKUP’s lookup range and return range are specified separately, so the return range can be to the left or right of the lookup range. They must still line up row by row.
Related
- Excel Data Run Analysis: A Practical Runs Test
- Excel Data Model in the Web: What Works and What Doesn’t
- Excel Data Analysis Toolkit: Setup, Tools, and Practical Uses
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Structured References in Excel Formulas: A Practical GuideStructured references are Excel table references that use a table’s name and column headings instead of ordinary cell…
- IFS Excel Formula Examples: Syntax, Rules, and TipsExcel’s IFS function checks conditions in order and returns the value linked to the first condition that is…
- Show Formulas in Excel on iPad: View Cell FormulasIn Excel on iPad, tap a cell to see its formula in the formula bar. To display formulas…
- Excel Table Array Lookup: Using VLOOKUP in a TableAn Excel table array lookup usually means using the table_array argument in VLOOKUP: it tells Excel which range…
- Excel INDIRECT Function: How to Use It with Cell ReferencesTo make Excel interpret text as a cell reference, use INDIRECT. With “B3” in A1 and 42 in…
- Excel COUNTIFS Not Equal to Text: Formula ExamplesTo count cells that are not equal to specific text, use COUNTIFS with the not-equal operator inside quotation…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.