How to Retrieve Data from Multiple Excel Sheets Without Errors
Question details
The user needs to consolidate data from multiple Excel worksheets, ensuring that missing entries return a blank cell instead of an error.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Combining data from several worksheets where some items (such as animal weights) may not exist on every individual sheet.
- Observed behavior
- Currently, looking up missing data results in error values. The goal is to return blank cells when the target data is not found.
Ensure that all your target worksheets are within the same workbook and that the sheet names listed in your summary header match exactly with the actual tab names.
Use an XLOOKUP and IFERROR Formula (Recommended)
A simpler and more maintainable method using modern lookup functions to pull data across sheets without displaying errors.
If you are using a modern version of Excel or WPS Spreadsheet, XLOOKUP provides a cleaner way to search across sheets dynamically. You can easily wrap it in IFERROR or use its built-in argument to handle missing data gracefully.
Create a summary sheet. Place your lookup values (e.g., animal names) in Column A, starting from A2. In Row 1, starting from B1, type the exact names of the target worksheets.
In cell B2, enter the formula: `=IFERROR(XLOOKUP($A2, INDIRECT(B$1&"!A:A"), INDIRECT(B$1&"!B:B")), "")`. This searches for the value in A2 within column A of the sheet specified in B1, and returns the corresponding value from column B.
Press Enter. Drag the fill handle from the bottom-right corner of cell B2 down to the end of your list, and then across to cover all the sheet names. Missing values will automatically appear as blank cells.
Use a Complex Array Formula (For Older Versions)
Utilize a combination of IFERROR, INDIRECT, and ADDRESS functions to dynamically pull data if your software does not support modern dynamic arrays.
Easily Combine Multiple Sheets in WPS Office
WPS Spreadsheet provides robust support for modern formulas like XLOOKUP and IFERROR, making it incredibly simple to consolidate data across multiple worksheets without encountering frustrating errors.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your multiple data sheets.
- 2. Apply your error-proof formula: Use the XLOOKUP or IFERROR functions in your master sheet to dynamically reference the other tabs.
- 3. Drag to fill: Use the fill handle to instantly copy your formula across the entire summary table, leaving missing data completely blank.
Frequently Asked Questions
Why do I get a #REF! error when using INDIRECT across multiple sheets?
A #REF! error usually occurs if the sheet name referenced in the INDIRECT formula does not exactly match the actual worksheet name, if there are unexpected spaces, or if the referenced sheet has been deleted. If your sheet names contain spaces, you must enclose the sheet reference in single quotes within your INDIRECT text string.
Can I use Power Query instead of formulas to combine sheets?
Yes, Power Query is highly recommended for combining multiple sheets in modern spreadsheet applications. You can use the 'Append Queries' or 'Merge' features, which automatically align headers and handle missing values by returning nulls (blanks) without needing complex IFERROR formulas.
How exactly does the IFERROR function work when combining data?
IFERROR evaluates your main lookup formula and checks the result. If the formula results in an error (like #N/A when a record isn't found), it outputs a specified fallback value instead. By setting this fallback value to "" (empty quotes), your missing data will appear as a clean, blank cell.
Do these multi-sheet lookup formulas work in WPS Office?
Yes, WPS Spreadsheet fully supports IFERROR, INDIRECT, and modern lookup functions like XLOOKUP, ensuring seamless formula execution and compatibility with your existing Excel workbooks.
