VBA & Macro Problems

How to Search Multiple Excel Tables and Display Combined Results

Maira Mehtab Sep 28, 2026 869 views

Question details

The user wants to create a search field in Excel that finds matching rows across separate tables on different worksheets and displays the consolidated results.

Product: Excel 2016
Device & OS: not provided
Scenario: Searching for specific data across multiple disparate tables with different row counts and headers, and consolidating the output into one cohesive view.
Observed behavior: Requires a custom VBA solution or advanced query setup, as native search functions do not extract and merge rows from multiple disconnected tables.

Before you start

Before creating a macro to search multiple tables, ensure your workbook is saved in a macro-enabled format (.xlsm) and that you have identified all the specific sheet names and table names you need to search.

Solution 1Recommended

Use a VBA Macro to Search and Combine Tables

Since built-in Excel functions cannot easily consolidate disparate tables with different headers on the fly, writing a custom VBA macro is the most effective approach to extract and combine matching rows.

VBA allows you to iterate through multiple worksheets, search for a specific term, and copy the matching rows to a unified 'Results' sheet.

Because your tables have different headers, your VBA code will need mapping instructions to place the data in the correct columns on the master sheet.

Enable the Developer Tab

Right-click the Excel ribbon, select 'Customize the Ribbon', and check the 'Developer' box in the right pane.

Open the VBA Editor

Navigate to the Developer tab and click 'Visual Basic', or press ALT + F11 on your keyboard.

Insert a New Module

In the VBA Editor, click 'Insert' from the top menu and select 'Module'.

Write the Search Loop

Create a Sub procedure that uses a 'For Each' loop to iterate through your target worksheets and tables. Use the 'Range.Find' method to locate the search term.

Map and Copy Results

Within your loop, write code to copy the matched rows. Explicitly state which column values from the source table correspond to the specific columns in your combined results sheet.

Community Support: Writing this specific macro requires custom programming based on your exact header names. It is highly recommended to post a detailed, reproducible question on a programming forum like Stack Overflow for tailored VBA code.

Solution 2

Use Power Query to Append Tables Before Searching

If you prefer not to use VBA, you can use Power Query to append the different tables into one master table, which can then be easily filtered or searched.

Run VBA Macros Seamlessly in WPS Spreadsheet

WPS Spreadsheet provides excellent support for VBA macros, allowing you to create custom search fields and consolidate multiple tables exactly as you would in Microsoft Excel.

1. Open Your File: Open your macro-enabled workbook (.xlsm) in WPS Spreadsheet.
2. Access Developer Tools: Navigate to the 'Developer' tab on the main ribbon.
3. Run or Edit Macros: Click 'Macros' to execute your consolidated search script, or click 'VBA Editor' to modify the code.

Fully compatible with Microsoft Excel .xlsm and .xlsb formatsBuilt-in VBA editor for writing, editing, and debugging macrosHigh performance for processing large datasets across multiple sheetsFamiliar user interface requiring zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't VLOOKUP work for searching multiple tables?

VLOOKUP is designed to search a single range or table. To search across multiple tables with varying structures, you need advanced tools like VBA, Power Query, or complex nested formulas with IFERROR.

Can I use the standard Excel Find (Ctrl+F) across multiple sheets?

Yes, you can press Ctrl+F, click 'Options', and change the 'Within' dropdown from 'Sheet' to 'Workbook'. However, this only highlights the individual cells one by one; it does not extract or combine the results into a single unified view.

Do I need to learn VBA to combine search results?

While VBA is highly customizable for this task, you can also use Power Query in modern versions of Excel (2016 and later) to append tables. Once appended, you can filter the unified table, which requires little to no coding.

How do I handle different headers when combining tables?

When using VBA, you must explicitly code which column from Table 1 maps to which column in your Results sheet. In Power Query, you simply rename the columns so they match identically before executing the Append command.

Excel & Data Problems