How to Look Up Data Across Different Excel Workbooks
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.

- 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.
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.
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.
Click on cell C2 in your first workbook where you want the matched result to appear.
Type =XLOOKUP(B2, '[File2.xlsx]Sheet1'!$A:$A, '[File2.xlsx]Sheet1'!$B:$B, "n/a") into the formula bar.
Replace 'File2.xlsx' and 'Sheet1' with the actual file name and worksheet name of your second workbook.
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 INDEX and MATCH as an Alternative
If you are using an older spreadsheet version that doesn't support XLOOKUP, the combination of INDEX, MATCH, and IFERROR is a robust alternative.
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. Launch WPS Spreadsheet: Open WPS Office and select WPS Spreadsheet to begin your work.
- 2. Open Both Workbooks: Open your source workbook and destination workbook. They will conveniently load as separate tabs within the same software window.
- 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. Complete and Drag: Press Enter to retrieve the data, and drag the fill handle to apply the formula across your dataset.

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.




