XLOOKUP Between Two Sheets: Examples and Common Fixes

To find a value on one worksheet and return its matching value on another, use XLOOKUP with the first sheet’s lookup cell and the second sheet’s lookup and return ranges. With the product code in Sheet1!A2, codes in Sheet2!A2:A100, and prices in Sheet2!B2:B100, enter =XLOOKUP(A2,Sheet2!$A$2:$A$100,Sheet2!$B$2:$B$100,"Not found") on Sheet1; if P-104 is in A2 and its price is 18.5 on Sheet2, the result is 18.5.

A basic XLOOKUP example between sheets

Imagine a workbook with an order sheet and a product list. The order sheet, named Sheet1, contains product codes in column A. The product list, Sheet2, has product codes in column A and prices in column B. You want the price to appear beside each order code without copying the product list onto the order sheet.

For the formula above, the first argument, A2, is the value to find. The second argument, Sheet2!$A$2:$A$100, is where Excel searches for that code. The third argument, Sheet2!$B$2:$B$100, is the corresponding range from which Excel returns a result. The optional fourth argument tells Excel what to show if it cannot find a match.

The dollar signs lock the lookup and return ranges when you copy the formula down. The reference A2 is relative, so it changes to A3, A4, and so on in successive rows. Select the result cell, enter the formula, press Enter, and use the fill handle to copy it down if you have more order rows.

How to refer to another worksheet

A worksheet reference has the general form SheetName!Range. The exclamation mark separates the worksheet name from the cell or range. In the example, Sheet2!$A$2:$A$100 means cells A2 through A100 on the worksheet named Sheet2.

If a sheet name contains spaces or certain punctuation, put the name in single quotation marks. For instance, if the lookup table is on a sheet called Product List, the formula’s ranges should use references such as ‘Product List’!$A$2:$A$100 and ‘Product List’!$B$2:$B$100. Excel often adds these quotation marks automatically when you select a range on a sheet with a spaced name.

You can also build the reference by typing the beginning of the formula, clicking the other worksheet tab, and selecting the range with the mouse. This is a useful way to avoid mistyping a sheet name. Return to the formula cell and finish entering the remaining arguments before pressing Enter.

Set up the lookup and return ranges correctly

The lookup array and return array must line up: each position in one range must correspond to the same row position in the other. If codes are in Sheet2!A2:A100, the matching prices should be in Sheet2!B2:B100—not B1:B99 or B3:B101. A mismatch can produce incorrect results or an array-size error.

Use one lookup column or row for the values you are searching for.
Use the corresponding column or row for the values you want returned.
Keep the data ranges the same height or width.
Include the entire data area, but avoid including headings unless the headings are also intended lookup values.

In a growing list, you might choose ranges that extend farther down than the current data, such as rows 2 through 1000, so new records are included. For large workbooks, bounded ranges or Excel tables can be easier to manage than entire-column references. If the source data is formatted as a table, structured references can make formulas more readable, although the exact formula depends on the table and column names.

Exact matches, missing values, and duplicates

XLOOKUP uses exact matching by default. That makes it suitable for product codes, employee IDs, invoice numbers, and other values that should match precisely. You do not need to add a separate exact-match setting for the basic example.

The fourth argument, if_not_found, controls what appears when no match exists. In the opening formula, it returns the text Not found instead of the default #N/A error. You can choose a different message, such as No match, or omit this argument if you prefer to see #N/A and handle it as an error. A readable message can make a report friendlier, but it can also hide unexpected data problems; use a message that makes the missing match clear.

If the lookup value appears more than once, XLOOKUP returns the first matching item it encounters, searching from the beginning by default. With records listed newest at the bottom, you can search from the end to return the last match. With the same Sheet1 and Sheet2 layout and P-104 appearing in both rows 10 and 40 of Sheet2, this formula returns the value from row 40:

With codes in Sheet2!A2:A100 and corresponding prices in Sheet2!B2:B100, enter this in Sheet1’s result cell. If P-104 occurs in rows 10 and 40 and the prices are 17 and 18.5, the result is 18.5.

=XLOOKUP(A2,Sheet2!$A$2:$A$100,Sheet2!$B$2:$B$100,"Not found",0,-1)

The final -1 sets the search direction to last-to-first; the 0 specifies exact matching. This does not sort the data or identify the newest record by date automatically. It simply returns the last matching row in the range’s current order, so confirm that the list is arranged in the way your task requires.

Return more than one column

XLOOKUP can return multiple adjacent columns from the matching row. This is handy when one code should bring back a product name, price, and status together. In Microsoft 365, the returned values can spill into neighboring cells; make sure those cells are empty or Excel may show a #SPILL! error.

With the product code in Sheet1!A2, codes in Sheet2!A2:A100, and the product name, price, and status in Sheet2!B2:D100, enter this formula in Sheet1!B2. If P-104’s row has Desk Lamp, 18.5, and Active in columns B through D, those three values appear across B2:D2.

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

Only the lookup column needs to be a single column; the return array may contain several columns, as long as its rows correspond to the lookup array. If the formula returns a spill error, check whether any destination cells already contain data, merged cells, or other content blocking the results.

Common errors and how to fix them

A #N/A result usually means Excel did not find an exact match, unless you supplied a custom missing-value message. Compare the lookup value with the source data closely. A code stored as text may not match an apparently identical number stored as a numeric value. Leading or trailing spaces, different punctuation, or a hidden character can also cause a mismatch. Check a few source cells and standardize the data if necessary.

A #VALUE! result can point to incompatible range sizes or a problem with the formula’s arguments. Confirm that the lookup and return arrays cover corresponding records. A #REF! result can mean a worksheet or cell reference was removed or changed. Check the sheet name and range in the formula bar.

If Excel displays the formula as text instead of calculating it, check whether the cell is formatted as Text. Change the cell format to General, then edit the formula and press Enter. Also make sure the formula begins with an equals sign and that Show Formulas has not been turned on. If the formula is correct but the answer seems stale, check whether calculation is set to Automatic under Formulas > Calculation Options.

Verify the sheet name, including spaces and punctuation.
Confirm that the lookup value exists and is the same data type as the source value.
Check that lookup and return ranges start and end on corresponding rows.
Look for duplicate keys if the returned record is not the one you expected.
For a multi-column return, clear any cells that block the spill range.

Version availability and alternatives

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including current Excel for Windows, Mac, and the web. The formula pattern is generally the same across those platforms. If someone opens the workbook in an older Excel version such as Excel 2019 or Excel 2016, XLOOKUP may not calculate there and may appear as an unsupported function.

For an older version, INDEX and MATCH can provide a comparable exact-match lookup, though the formula is longer. VLOOKUP is another option when the lookup column is at the left of the return column, but it searches vertically and uses a column index number. XLOOKUP is often easier to read because the lookup range and return range are specified separately, and the return range can be to either side of the lookup range.

For simple matching between sheets, there is usually no need to copy both lists onto one sheet or sort them first. XLOOKUP does not require the lookup list to be sorted for its default exact match. Approximate matches are available through match-mode options, but they serve a different purpose, such as finding a value within ordered thresholds; use them only when that behavior is intended.

Frequently Asked Questions

Can I use XLOOKUP between two different worksheets in the same workbook?
Yes. Put the lookup value on one worksheet and refer to the lookup and return ranges on the other using references such as Sheet2!$A$2:$A$100. Add single quotation marks around a sheet name that includes spaces.

Do the two sheets need to be sorted?
No, not for XLOOKUP’s default exact-match behavior. The formula searches the lookup array for the value. Sorting matters only if you deliberately use approximate matching or depend on the order for a last-to-first search.

Why does XLOOKUP return #N/A when the values look the same?
The values may differ in a way that is not obvious, such as a number stored as text, an extra space, or a different character. Check the underlying cell contents and make the lookup and source values consistent.

Can XLOOKUP search another workbook?
Yes. A reference can point to a range in a different workbook, but the link must refer to the correct file and worksheet. Excel may ask whether to update links when the source workbook is moved or unavailable; keeping source files in stable locations helps avoid broken references.

What does XLOOKUP return if there are duplicate values?
By default, it returns the first match in the lookup range. Set the search direction to last-to-first if you need the last occurrence, and make sure the row order represents the record you intend to retrieve.

Can I copy the formula down a column?
Yes. Use absolute references for the source ranges, as in $A$2:$A$100, while leaving the lookup cell relative, as in A2. When you copy down, the lookup cell changes by row but the source ranges stay fixed.

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.