Function Problems

How to Look Up Data Across Different Excel Workbooks

Bushra ParveenBushra Parveen Nov 11, 2026 872 views

Question details

The user needs to match personnel numbers from one workbook with those in another workbook, output the corresponding value, and display "n/a" for missing numbers.

How to Look Up Data Across Different Excel Workbooks
Product
Spreadsheet
Device & OS
not provided
Scenario
Cross-referencing data between two separate spreadsheet files to find matching records and merge specific column values.
Observed behavior
Requires a formula to automate cross-workbook data retrieval and handle missing values gracefully without manual copy-pasting.
Before you start

Ensure both workbooks are open in your spreadsheet program before you begin writing the formulas; this allows you to easily select ranges and prevents external link path errors.

Solution 1Recommended

Use the XLOOKUP Function to Match Data

XLOOKUP is a modern, flexible function that simplifies cross-workbook lookups and natively handles missing values.

The XLOOKUP function is the most efficient way to perform cross-workbook lookups. It automatically replaces older functions like VLOOKUP and directly incorporates a built-in argument for handling cases where data is not found.

1
Select the destination cell

Click on cell C2 in your first workbook where you want the matched result to appear.

2
Enter the XLOOKUP formula

Type =XLOOKUP(B2, '[File2.xlsx]Sheet1'!$A:$A, '[File2.xlsx]Sheet1'!$B:$B, "n/a") into the formula bar.

3
Update the workbook references

Replace 'File2.xlsx' and 'Sheet1' with the actual file name and worksheet name of your second workbook.

4
Apply to the entire column

Press Enter to see the result. Then, double-click or drag the fill handle at the bottom right of cell C2 downwards to apply the formula to the rest of the column.

Use the XLOOKUP Function to Match Data
Pro Tip: Instead of typing the external workbook name manually, you can simply type '=XLOOKUP(B2,' and then use your mouse to navigate to the second workbook window to highlight columns A and B directly.
Efficient Data Analysis

Easily Look Up Data Across Workbooks in WPS Spreadsheet

WPS Spreadsheet provides full support for advanced lookup functions like XLOOKUP, VLOOKUP, and INDEX/MATCH. You can seamlessly cross-reference data across multiple workbooks with its highly intuitive tabbed interface.

  1. 1. Launch WPS Spreadsheet: Open WPS Office and select WPS Spreadsheet to begin your work.
  2. 2. Open Both Workbooks: Open your source workbook and destination workbook. They will conveniently load as separate tabs within the same software window.
  3. 3. Build the Formula: Type =XLOOKUP( in your destination cell, click on the other workbook tab to easily select your lookup arrays without manually typing external file paths.
  4. 4. Complete and Drag: Press Enter to retrieve the data, and drag the fill handle to apply the formula across your dataset.
Fully compatible with Microsoft Excel formulas and .xlsx file formats.Supports modern spreadsheet functions including XLOOKUP for efficient data retrieval.Free, lightweight, and features a unique tabbed interface to easily switch between multiple open workbooks.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my cross-workbook formula return a #REF! error?

A #REF! error usually occurs if the referenced workbook is closed and its path is incomplete, or if the source file has been moved, renamed, or deleted. Ensure the file path is correct and the source workbook is accessible.

Can I use VLOOKUP instead of XLOOKUP for this task?

Yes, you can use VLOOKUP wrapped in an IFERROR function, like this: =IFERROR(VLOOKUP(B2, '[File2.xlsx]Sheet1'!$A:$B, 2, FALSE), "n/a"). However, XLOOKUP is generally preferred as it is more flexible and does not break if you insert new columns.

How do I update external links if the second workbook's name changes?

Go to the 'Data' tab on the ribbon and click on 'Edit Links'. From there, you can select the old source file and click 'Change Source' to update the reference to the new workbook name.