How to Use XLOOKUP in Excel With Two Workbooks: Easy Guide

Sharing is caring!

If you keep data in separate Microsoft Excel files, you may need to pull information from one workbook into another. Instead of copying and pasting data manually, you can use XLOOKUP in Excel with two workbooks.

The XLOOKUP function can search for a value in one Excel workbook and return matching information from another workbook. It is especially useful for working with customer lists, employee records, product prices, inventory data, sales reports, and financial spreadsheets.

In this guide, you will learn how to use XLOOKUP between two Excel workbooks, including the correct formula structure and common errors to avoid.

How XLOOKUP Works Between Two Workbooks

Imagine you have two Excel files:

Workbook 1: Sales.xlsx
Workbook 2: Products.xlsx

The Sales workbook contains product IDs, but you want to automatically retrieve the corresponding product prices from Products.xlsx.

Your files might look like this:

Sales.xlsx

Products.xlsx

Using XLOOKUP across workbooks, Excel can search Products.xlsx for each Product ID and return the correct price into Sales.xlsx.

How to Use XLOOKUP in Excel With Two Workbooks

The easiest method is to keep both Excel workbooks open while creating the formula.

Step 1: Open Both Excel Workbooks

Open the workbook where you want the result to appear.

Then open the workbook containing your source data.

For example:

• Sales.xlsx will contain the XLOOKUP formula.
• Products.xlsx will contain the lookup table.

Keeping both files open makes it easier to select the correct cell ranges without manually typing external workbook references.

Step 2: Select the Cell for the XLOOKUP Formula

In Sales.xlsx, select the first cell where you want the returned value.

For example, suppose:

• Product ID is in cell A2
• Price should appear in C2

Click cell C2 and start the formula:

=XLOOKUP(A2,

Here, A2 is the lookup value.

Step 3: Select the Lookup Range in the Second Workbook

Switch to Products.xlsx.

Select the cells containing the Product IDs.

For example:

A2:A100

Excel automatically adds the external workbook reference to your formula.

It may look similar to:

'[Products.xlsx]Sheet1'!$A$2:$A$100

Step 4: Select the Return Range

Add a comma, then select the range containing the values you want returned.

If product prices are stored in column B, select:

B2:B100

Your complete formula may look like this:

=XLOOKUP(A2,'[Products.xlsx]Sheet1'!$A$2:$A$100,'[Products.xlsx]Sheet1'!$B$2:$B$100)

Press Enter.

Excel searches for the Product ID from A2 inside Products.xlsx and returns its matching price.

Step 5: Copy the Formula Down

Once the first XLOOKUP formula works, drag the fill handle down the column.

Because the external ranges use absolute references, such as $A$2:$A$100, Excel keeps the lookup ranges fixed while changing the lookup value.

For example:

A2 becomes A3, A4, A5, and so on.

This allows you to retrieve data for hundreds of rows quickly.

Add a Custom Message When No Match Is Found

If Excel cannot find the lookup value, XLOOKUP normally returns the #N/A error.

You can use the optional if_not_found argument to display a cleaner message.

For example:

=XLOOKUP(A2,'[Products.xlsx]Sheet1'!$A$2:$A$100,'[Products.xlsx]Sheet1'!$B$2:$B$100,"Not Found")

Now Excel displays Not Found instead of #N/A when no matching Product ID exists.

You could also use:

"No Match"

or:

""

Using empty quotation marks returns a blank cell when no result is available.

Using XLOOKUP When the Other Workbook Is Closed

XLOOKUP can reference another Excel workbook even after that source file is closed.

However, the formula will usually include the full file path.

For example:

=XLOOKUP(A2,'C:\Reports\[Products.xlsx]Sheet1'!$A$2:$A$100,'C:\Reports\[Products.xlsx]Sheet1'!$B$2:$B$100)

You normally do not need to type this path yourself. Excel updates the external reference automatically after the source workbook is saved and closed.

Avoid moving or renaming the source workbook unnecessarily because doing so may cause broken external links.

XLOOKUP Between Two Workbooks Using Excel Tables

If your data changes regularly, converting the source range into an Excel Table can make the workbook easier to manage.

Suppose your table in Products.xlsx is named ProductTable with columns called ProductID and Price.

Your formula could use structured references such as:

=XLOOKUP(A2,ProductTable[ProductID],ProductTable[Price],"Not Found")

Tables automatically expand when new rows are added, reducing the need to manually update lookup ranges.

External table references may become longer when referencing another workbook, but the basic XLOOKUP logic remains the same.

Common XLOOKUP Problems With Two Workbooks

Several issues can prevent an external XLOOKUP formula from working correctly.

Another common problem occurs when one workbook stores numbers as text while another stores them as actual numbers. For example, 101 and "101" may look identical but can behave differently in lookup formulas.

Keeping your data types consistent helps XLOOKUP match values correctly.

XLOOKUP vs VLOOKUP for Two Workbooks

Both functions can retrieve information from another Excel file, but XLOOKUP provides more flexibility.

With VLOOKUP, the lookup column generally needs to appear on the left side of the table. XLOOKUP removes this limitation because you select the lookup array and return array separately.

Best Practices for XLOOKUP Across Workbooks

When using XLOOKUP with external workbooks, keep your files organized. Store related Excel files in stable folders and avoid changing their names frequently.

Use absolute cell references for source ranges so they do not move when formulas are copied. If your source data grows regularly, consider using Excel Tables instead of fixed ranges.

It is also helpful to use clear worksheet names such as Products, Inventory, or Employees instead of generic names like Sheet1.

Finally, always check a few returned values manually after creating the formula. This helps confirm that Excel is matching the correct records.

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.