Function Problems

How to Simplify a Lookup Across Multiple Excel Worksheets and Columns

Maira Mehtab Sep 21, 2026 871 views

Question details

The user needs to search for an input value across three columns containing old and new part numbers spanning approximately ten external worksheets, and return the current part number from the final column without keeping the network workbook open or using the INDIRECT function.

Product: Excel
Device & OS: not provided
Scenario: Performing a lookup against multiple columns across several external worksheets while avoiding volatile functions that require the source workbook to remain open.
Observed behavior: The user wants to retrieve data effectively but faces limitations with the INDIRECT function, which returns errors when external network workbooks are closed.

Before you start

Ensure you have read access to the external network workbook and consider making a temporary local copy to prevent accidental edits during the data consolidation process.

Solution 1Recommended

Consolidate Worksheets into a Single Master List

Merge the data from all ten worksheets into a single master list to perform a straightforward lookup without needing the INDIRECT function.

Because the INDIRECT function requires external workbooks to remain open, the most robust approach is to consolidate your data. By appending the data from the various sheets into one master list, you can use standard lookup functions while retaining the original separated sheets for display purposes.

Open the external workbook

Access the network location and open the external workbook containing the ten separate worksheets.

Create a Master List sheet

Insert a new worksheet into the workbook and name it 'Master List'.

Append the data

Copy the three columns (old and new part numbers) from each of the ten worksheets and paste them vertically into the 'Master List' sheet, one under the other. Alternatively, use Power Query via Data > Get Data > From File > From Workbook to automatically append all sheets into one table.

Perform the lookup

In your primary workbook, write an INDEX/MATCH or XLOOKUP formula targeting the unified columns in the 'Master List' sheet. For example: =XLOOKUP(SearchValue, '[ExternalBook.xlsx]Master List'!$A$1:$A$1000, '[ExternalBook.xlsx]Master List'!$C$1:$C$1000).

Maintain Original Structure: You can keep the original ten worksheets exactly as they are for users who need to view them, while using the consolidated master list purely as a backend data source for your formulas.

Efficient Data Processing with WPS Spreadsheet

Consolidate and Lookup Data Easily in WPS Office

WPS Spreadsheet offers powerful data consolidation tools and advanced lookup functions, making it simple to search across extensive datasets and external workbooks without complex workarounds.

1. Open your files in WPS Spreadsheet: Launch WPS Office and open both your working file and the external data workbook.
2. Consolidate your data: Navigate to the Data tab and use the Consolidate tool, or simply copy and append your columns into a newly created master sheet.
3. Initiate the XLOOKUP function: Click the cell where you want the final part number to appear and type =XLOOKUP(.
4. Select ranges and execute: Select your input cell, highlight the lookup column in your master sheet, select the return column, and press Enter to complete the multi-sheet lookup.

Seamlessly consolidate data from multiple worksheets into a single master sheet.Fully compatible with Microsoft Excel formulas, functions, and file formats (.xlsx).Built-in advanced lookup functions like XLOOKUP for multi-column searches.Lightweight software architecture that handles large network files efficiently.

microsoft office alternative - wps office

Frequently Asked Questions

Why does INDIRECT not work with closed external workbooks?

The INDIRECT function in Excel is a volatile function that requires the referenced external workbook to be open in the background to dynamically resolve the cell address. If the target workbook is closed, the formula cannot calculate and returns a #REF! error.

Can I use Power Query instead of manually copying the data?

Yes, Power Query is an excellent, automated alternative. You can use the 'Get Data' feature to import the workbook, then append all the worksheets into one dynamic master table that updates automatically whenever the source data is modified.

How do I lookup a value across three different columns?

If you need to search an input against three separate columns, you can nest multiple XLOOKUP functions using IFERROR. For example: =IFERROR(XLOOKUP(Input, Col1, ReturnCol), IFERROR(XLOOKUP(Input, Col2, ReturnCol), XLOOKUP(Input, Col3, ReturnCol))). This checks each column sequentially until a match is found.

Excel & Data Problems