XLOOKUP in Excel: Find and Return Data From Another Sheet

Yes. XLOOKUP can search a range on one worksheet and return the matching value from another range on that sheet; the sheet name is part of the range reference. With the lookup ID in A2, IDs in Products!A2:A100, and product names in Products!C2:C100, enter this formula on your current sheet to return the matching name, or “Not found” if the ID is absent:

=XLOOKUP(A2,Products!$A$2:$A$100,Products!$C$2:$C$100,"Not found")

For example, if A2 contains P-104 and Products!A6 contains P-104 while Products!C6 contains Desk lamp, the result is Desk lamp. The lookup value can be on the formula’s sheet, and the lookup and return ranges can both be on a different sheet.

How a cross-sheet XLOOKUP works

XLOOKUP compares a lookup value with items in a lookup array, then returns the value at the corresponding position in a return array. Those arrays do not need to be on the worksheet where you enter the formula. Excel uses the worksheet name and an exclamation mark to identify a range on another sheet, such as Products!A2:A100.

The formula’s four main parts are the value to find, the range to search, the range to return a result from, and an optional message for a missing match. The lookup and return ranges should line up: if the lookup range has 99 rows, the return range should cover the corresponding 99 rows. XLOOKUP normally finds an exact match, so you do not have to add a special argument for exact matching in a typical ID or name lookup.

Dollar signs in a reference, as in $A$2:$A$100, lock the range when you copy the formula. They do not change where the range is located. A reference such as Products!$A$2:$A$100 means cells A2 through A100 on the worksheet named Products.

Set up the sheets and enter the formula

Suppose your current sheet is Orders and contains a product ID in A2. The Products sheet has IDs in column A and product names in column C. Enter the XLOOKUP formula in the Orders cell where you want the name to appear. You can type the reference directly, or build it by selecting the ranges:

Click the result cell on the sheet where you want the answer.
Type =XLOOKUP(, then select the cell containing the ID to look up.
Type a comma, click the Products sheet tab, and select the ID range.
Type another comma and select the product-name range on Products.
Optionally add a not-found message, close the parenthesis, and press Enter.

For example, with the ID in Orders!A2, the product IDs in Products!A2:A100, and names in Products!C2:C100, this formula returns the name corresponding to the ID; if the ID is P-104 and that row’s name is Desk lamp, the result is Desk lamp:

=XLOOKUP(A2,Products!$A$2:$A$100,Products!$C$2:$C$100,"Not found")

In Excel for Microsoft 365 on Windows, these steps and formula work in the desktop app. Excel for the web also supports XLOOKUP, though the interface for selecting and editing ranges may look different. On Mac, the formula is the same; use the worksheet tabs and formula bar as needed. Commas are the argument separators in the examples. Excel installations configured for a region that uses semicolons may require semicolons instead.

Use a sheet name with spaces

Sheet names containing spaces or certain punctuation need single quotation marks around the name in a formula reference. For a sheet named Product List, Excel’s reference takes the form ‘Product List’!A2:A100. The apostrophes belong around the sheet name, not around the cell range.

With the lookup ID in Orders!A2, IDs in Product List!A2:A100, and names in Product List!C2:C100, use this formula; if the matching row has the name Desk lamp, it returns Desk lamp:

=XLOOKUP(A2,'Product List'!$A$2:$A$100,'Product List'!$C$2:$C$100,"Not found")

When you create a reference by clicking a sheet tab and selecting cells, Excel generally adds the necessary quotation marks automatically. If you rename a worksheet within the same workbook, Excel ordinarily updates formulas that refer to it. If a reference was typed manually or copied from another file, check the spelling of the sheet name and the punctuation if Excel reports a problem.

Return more than one column

XLOOKUP can return a whole row segment, not just one column. In Microsoft 365 and supported newer Excel versions, a formula can spill results into neighboring empty cells. This is useful when one ID should return a product name and price together, for example. Leave the destination cells to the right of the formula clear so Excel has room to display the results.

With the lookup ID in Orders!A2, product IDs in Products!A2:A100, and product names and prices in Products!C2:D100, enter this in an empty result cell. If P-104 matches the row containing Desk lamp and 24.99, the two returned values spill into adjacent cells:

=XLOOKUP(A2,Products!$A$2:$A$100,Products!$C$2:$D$100,"Not found")

If the spill area is blocked by existing data, Excel displays a #SPILL! error rather than overwriting those cells. Clear the obstructing cells or move the formula. If you need to fill separate columns individually, you can use a separate XLOOKUP in each output column, changing the return range each time.

Choose options for missing values and match behavior

The fourth argument, called if_not_found, lets you replace the default #N/A result with a message or another value. A short message such as “Not found” can be easier for workbook users to understand, but it can also conceal an unexpected mismatch. During setup, leaving the default error visible may help identify IDs that do not match; once the workbook is ready, decide whether a message is more useful.

XLOOKUP uses exact matching by default. Its optional match_mode argument can change that behavior: 0 means exact match, -1 means exact match or the next smaller item, 1 means exact match or the next larger item, and 2 enables wildcard matching. Approximate matching is useful for ordered thresholds, such as assigning a rate based on a score, but the data and intended rule must be clear. Do not use approximate matching for ordinary product codes simply to make a failed exact match return something.

With a search value in A2 and ascending minimum thresholds in Rates!A2:A6, with the corresponding rate labels in Rates!B2:B6, this formula returns the label for the exact threshold or the next smaller threshold. If A2 is 72 and the largest threshold not exceeding it is 60, the result is the label in B4:

=XLOOKUP(A2,Rates!$A$2:$A$6,Rates!$B$2:$B$6,"No rate",-1)

This approximate example depends on thresholds being arranged and interpreted as intended. For exact lookups, leave match_mode out rather than adding an approximate option without a reason.

Lookups in another workbook

A different worksheet in the same workbook is not the same thing as a different Excel file. For another worksheet, the reference includes the sheet name, as in Products!A2:A100. For a separate workbook, the reference also identifies the external workbook, and Excel may include its file name and path. One practical way to create that reference is to open both workbooks, start the formula, and select the source cells in the other workbook; Excel builds the external reference.

External links can become inconvenient if a source file is moved, renamed, or unavailable. Excel may ask to update links when a workbook opens, and a formula can continue to show a previously saved value when its source cannot be refreshed. Keep linked files in a stable location, and review workbook link prompts instead of assuming every external value is current. If both data sets can reasonably live in one workbook, separate worksheets usually make references easier to maintain.

Common errors and ways to fix them

A cross-sheet formula can be syntactically correct and still fail to find a match. Check the value and the source data before changing the formula. Common causes include:

#N/A: No exact match was found. Confirm the lookup value exists and is in the range being searched.
Numbers stored as text: A numeric-looking ID stored as text may not match the same digits stored as a number. Make the data types consistent rather than relying on visual appearance.
Extra spaces or hidden characters: Names copied from outside sources may contain leading, trailing, or nonprinting characters. Clean the source values if they are meant to represent the same key.
Wrong sheet or column: Verify the sheet name, lookup range, and return range. A valid formula can return a value from the wrong column if the selected range is off by one.
Duplicate lookup values: XLOOKUP returns the first matching item it encounters in its search direction. If IDs should be unique, identify and resolve duplicates rather than assuming the last duplicate will be returned.
#NAME?: The Excel version may not recognize XLOOKUP, or the function name may be misspelled. Check the version and the formula spelling.
#SPILL!: A multi-column result cannot expand because one or more destination cells are occupied. Clear the spill range or choose another location.

For a difficult mismatch, test one known lookup value and inspect its corresponding source row. Also confirm that the lookup range and return range start and end on corresponding rows. Avoid using entire columns automatically in very large or complicated workbooks if bounded ranges or Excel tables can make the formula easier to inspect and maintain.

XLOOKUP compared with VLOOKUP and alternatives

Compared with VLOOKUP, XLOOKUP makes the lookup range and return range separate arguments. This means the return range can be to the left or right of the lookup range, and inserting a column between them is less likely to make the formula return the wrong field. XLOOKUP also defaults to an exact match and provides a direct not-found argument.

VLOOKUP remains relevant when a workbook must work in older Excel versions that do not include XLOOKUP. INDEX and MATCH can also perform flexible lookups in older versions, but their formulas are less direct for many readers. Before replacing a legacy formula, consider who will open the workbook: a formula that works in Microsoft 365 may not calculate in Excel 2019 or Excel 2016.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. In Excel 2019 and Excel 2016, it is not available as a built-in function, so a workbook using it may show #NAME? or fail to calculate correctly there. If compatibility matters, use a function supported by the oldest version in your audience or confirm that everyone has a version that supports XLOOKUP.

Frequently Asked Questions

Can XLOOKUP look across two different sheets?
Yes. Put the lookup value where convenient, then reference the lookup and return ranges on the other sheet with its name followed by an exclamation mark. If the sheet name has spaces, wrap it in single quotation marks.

Does XLOOKUP work between different Excel files?
Yes. XLOOKUP can use ranges in another workbook through an external reference. The source file may need to remain available at its expected location for Excel to refresh the linked values reliably.

Why does my cross-sheet XLOOKUP return #N/A?
Usually, Excel cannot find an exact match for the lookup value in the specified range. Check for mismatched text and number types, extra spaces, a wrong lookup range, or a value that is genuinely absent.

Do I need to use quotation marks around a sheet name?
Use single quotation marks when the sheet name contains spaces or certain special characters, as in ‘Product List’!A2:A100. A simple name such as Products can be referenced without them.

Can XLOOKUP return multiple columns from another sheet?
Yes. Use a multi-column return range. In Microsoft 365 and supported versions, the results spill into adjacent cells, so those cells must be empty.

What can I use instead of XLOOKUP in Excel 2019?
Excel 2019 does not include XLOOKUP. VLOOKUP can work when the return column is to the right of the lookup column, while INDEX with MATCH can handle more flexible layouts.

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.