Excel XLOOKUP Between Two Worksheets: Examples

If your lookup value is on one worksheet and the data to search is on another, XLOOKUP can connect them directly. With order IDs in Orders!A2:A100 and IDs and product names in Products!A2:B50, enter =XLOOKUP(A2,Products!$A$2:$A$50,Products!$B$2:$B$50,"Not found") in Orders!C2; if A2 contains P-104 and that ID belongs to Desk Lamp, the result is Desk Lamp.

How XLOOKUP works across worksheets

XLOOKUP searches one range for a value and returns a corresponding value from another range. The lookup range and return range can be on different worksheets in the same workbook; the formula simply names the worksheet before the range. In the opening example, A2 is the value to find, Products!$A$2:$A$50 is the search range, and Products!$B$2:$B$50 contains the results.

The dollar signs make the two ranges absolute references. When you copy the formula down the Orders sheet, the lookup cell changes from A2 to A3, A4, and so on, while the product ranges stay fixed. XLOOKUP returns the corresponding item from the same relative position in the return range. Unlike older lookup patterns, the return range does not have to be to the right of the lookup range.

XLOOKUP uses exact matching by default. It searches from the first item to the last item unless you specify otherwise, and it returns the first match it finds. The function is available in Microsoft 365 and Excel 2021 or later, including Excel for Windows, Mac, and the web. Excel 2019 and earlier do not include XLOOKUP, so those versions need an alternative such as INDEX and MATCH.

Prepare the worksheets and ranges

Before writing the formula, identify which column contains the value you already know and which column contains the answer you need. For example, the Orders sheet might have an order ID in column A and a blank Product column in C. The Products sheet might store product IDs in A and product names in B. The two ID columns need to use compatible values: an ID stored as text may not match an apparently identical ID stored as a number.

Use a clear, consistent layout and avoid including column headers in the lookup range. A simple setup process is:

Confirm the lookup value appears in the source data, such as an order ID or SKU.
Choose a lookup range containing that key and a return range containing the desired result.
Make sure both ranges cover the same number of rows and align row by row.
Decide what the formula should display when the key is missing, such as Not found or a blank cell.

The ranges in the examples below end at row 50 to keep the formulas readable. Adjust the endpoints to fit your data. If records are regularly added, consider converting the source range into an Excel table so the data can expand without manually changing fixed row limits.

Build and copy a basic cross-sheet formula

With order IDs in Orders!A2:A100, product IDs in Products!A2:A50, and product names in Products!B2:B50, place this formula in Orders!C2 and fill it down. It returns Desk Lamp when A2 is P-104 and Products!A8 contains P-104 while Products!B8 contains Desk Lamp; the exact row depends on where that record sits in the source list.

To enter it, select Orders!C2, type the formula, and press Enter. Use the fill handle at the lower-right corner of the cell to copy it down, or copy the cell and paste into the remaining rows. The A2 reference is relative, so it updates for each order. The dollar signs keep the Products ranges fixed.

If a worksheet name contains spaces or certain punctuation, enclose it in single quotation marks. With order IDs in Orders!A2:A100 and IDs and product names in columns A and B of a worksheet named Product List, use this formula in Orders!C2:

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

For a worksheet named Products with no spaces, the single quotation marks are optional. They do not change the lookup result. Worksheet names must still be spelled correctly; a renamed or misspelled sheet reference can cause a reference error.

Useful XLOOKUP examples between worksheets

Return a value from a column to the left. XLOOKUP can search in either direction. With product IDs in Products!B2:B50 and product names in Products!A2:A50, and an ID to find in Orders!A2, put this in Orders!C2. If A2 contains P-104 and the matching row has Desk Lamp in column A, the formula returns Desk Lamp:

=XLOOKUP(A2,Products!$B$2:$B$50,Products!$A$2:$A$50,"Not found")

Return several adjacent columns. If IDs are in Products!A2:A50 and product name, category, and unit price are in Products!B2:D50, enter this in Orders!C2. For P-104, if the matching source row contains Desk Lamp, Lighting, and 24.99, the three results spill into C2:E2. Leave those spill cells empty, or Excel may show a #SPILL! error.

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

Return the last matching record. If a product ID appears more than once in Products!A2:A50 and dates or status values are in B2:B50, this formula in Orders!C2 searches from the bottom upward. If P-104 appears in rows 8 and 22 and B22 contains Active, the result is Active. The final -1 sets the search direction to last-to-first.

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

Find a partial text match. If Orders!A2 contains a fragment such as Lamp, and Products!A2:A50 contains product names with corresponding SKUs in B2:B50, this formula in Orders!C2 returns the SKU from the first name containing Lamp. The asterisks are wildcards, and match mode 2 enables wildcard matching.

=XLOOKUP("*"&A2&"*",Products!$A$2:$A$50,Products!$B$2:$B$50,"Not found",2)

Wildcard matching is for text searches, not a replacement for a reliable unique key. If several names contain the fragment, the formula returns the first match in the search order, which may not be the intended item.

Choose exact, approximate, or wildcard matching

For IDs, names, and most routine cross-sheet lookups, use exact matching. It is XLOOKUP’s default, so the basic formulas above do not need an extra match-mode argument. Exact matching is especially important when a partial or near match could return a different person, product, or record.

For a deliberate approximate lookup, XLOOKUP offers match modes for the next smaller or next larger item. For instance, with scores in Grades!A2:A5 sorted as 0, 60, 70, and 80, and labels in Grades!B2:B5 as Not passed, Pass, Merit, and Distinction, this formula in Results!B2 returns Merit for a score of 75 in Results!A2. Match mode -1 requests an exact match or the next smaller item. Use approximate matching only when the lookup data and desired thresholds are understood; keep threshold values in the correct order and verify boundary cases.

=XLOOKUP(A2,Grades!$A$2:$A$5,Grades!$B$2:$B$5,"No band",-1)

Wildcard matching is useful when the lookup text is incomplete, but it can be ambiguous. For predictable results, prefer exact IDs, and use a wildcard only when a partial-text search is genuinely what the task requires.

Common errors and how to fix them

#N/A or the not-found message: Check for extra spaces, misspelled keys, different data types, and an incorrect source range. XLOOKUP’s fourth argument controls the message shown when no match is found. A not-found message is useful, but it should not conceal a badly chosen range.
#REF!: Confirm that the referenced worksheet still exists and that its name is correct. If the sheet name has spaces, put it in single quotation marks.
#VALUE!: Check that the lookup and return ranges have compatible dimensions. For a single-result lookup, use ranges with corresponding rows; for a multi-column return, ensure the return array begins on the same source row as the lookup range.
Wrong match despite a plausible result: Look for duplicate keys. Exact-match XLOOKUP returns the first match in its search direction, not every matching row. Use a last-to-first search when the latest or last listed match is intended, or use a different approach if you need all matching records.
#SPILL!: When returning multiple columns, clear cells in the area where the results should spill. A merged cell or existing content can block the output.
Formula appears as text or is rejected: Check that the cell is not formatted as Text and that the formula begins with an equals sign. Some regional settings use semicolons rather than commas as argument separators; if Excel expects semicolons, use the separator indicated by that installation.

Also check whether a key contains leading or trailing spaces. The values can look identical on screen but fail to match. Cleaning the source data is usually better than adding complicated workarounds to every lookup formula.

When to use a table or another lookup method

Fixed ranges such as Products!$A$2:$A$50 are easy to understand, but new records added below row 50 are not included. For a growing source list, convert the data to a table by selecting it and choosing Insert > Table. Tables expand as rows are added and can make formulas easier to read. For example, if the table is named ProductCatalog and its columns are SKU and ProductName, with the lookup value in Orders!A2, use this in Orders!C2:

=XLOOKUP(A2,ProductCatalog[SKU],ProductCatalog[ProductName],"Not found")

If your Excel version does not have XLOOKUP, INDEX and MATCH can perform an exact lookup. With the same product IDs and names in Products!A2:A50 and Products!B2:B50, and the order ID in Orders!A2, this formula in Orders!C2 returns the matching name:

=INDEX(Products!$B$2:$B$50,MATCH(A2,Products!$A$2:$A$50,0))

The final 0 tells MATCH to look for an exact match. INDEX and MATCH work in older Excel versions, but they do not provide XLOOKUP’s built-in not-found argument; the formula may return #N/A when there is no match. VLOOKUP is another older option, but it generally searches the first column of its table and returns a column to its right, so it is less flexible when the answer is to the left of the key.

Frequently Asked Questions

Can XLOOKUP search another worksheet in the same workbook?
Yes. Add the worksheet name and an exclamation mark before the range, such as Products!$A$2:$A$50. Use single quotation marks around worksheet names with spaces, as in ‘Product List’!$A$2:$A$50.

Can XLOOKUP return a value to the left of the lookup column?
Yes. Set the lookup array to the column containing the key and the return array to the column containing the desired result. XLOOKUP does not require the return column to be on the right.

How do I show a blank when there is no match?
Use an empty text string as XLOOKUP’s fourth argument. With the lookup value in Orders!A2 and product IDs and names in Products!A2:A50 and B2:B50, this formula in Orders!C2 returns the name if found and a blank if it is not:

=XLOOKUP(A2,Products!$A$2:$A$50,Products!$B$2:$B$50,"")

Can XLOOKUP use a worksheet in a different workbook?
Yes, provided the external workbook and worksheet references are valid. The simplest way to create one is to start the formula, select the range in the other workbook, and let Excel build the reference. External links may need updating if the source file is moved or renamed, so a same-workbook source is simpler when practical.

Why does XLOOKUP return the wrong duplicate?
By default, it returns the first match from the top. If the last matching row is the one you need, set match mode to 0 and search mode to -1, as shown in the last-match example. If duplicates represent separate records that all need to be shown, a one-result lookup is not sufficient.

Practical checks before relying on the result

A cross-sheet lookup is only as dependable as its key and source data. Before filling the formula down a large list, test a known match, a missing key, and—if duplicates exist—a repeated key. Confirm that the returned value belongs to the intended record. If the workbook is shared with people using Excel 2019 or earlier, remember that those versions cannot calculate XLOOKUP and may display a function-name error; use INDEX and MATCH when compatibility with those editions is necessary.

On Windows, the formula syntax and worksheet references work in Excel for Microsoft 365. The same XLOOKUP formulas are available in Excel for Mac and Excel for the web; the worksheet names and ranges do not need a different formula just because the platform changes. If data is likely to grow, use a table or deliberately expand the ranges, and document what the not-found result means so readers do not mistake a missing match for a legitimate blank.

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.