Excel Multiple Sheet Search Bar

In today’s email:

1. Search across multiple tables simultaneously

2. Show results instantly as you type

3. Work with partial matches (like typing "am" to find "Amazon")

4. Stack all the results together in one clean view

MAIN ARTICLE

VIDEO WALKTHROUGH

How to Create a Dynamic Search Bar Across Multiple Excel Tables

Ever needed to search through data spread across different Excel sheets? Let's walk through creating a dynamic search bar that can look through multiple tables at once. This is perfect when you're dealing with large datasets split across different worksheets.

A standard search function might work for a single table, but what if you need to search through multiple datasets at once? In this tutorial, we’ll build a dynamic search bar that pulls data from multiple sheets, combining the results into one seamless list.

Step 1: Understanding the Basics of Excel’s FILTER Function

The FILTER function in Excel allows us to extract specific rows from a dataset based on defined criteria. In this case, we’ll use a search term to filter company names from multiple sheets.

A basic FILTER function looks like this:

=FILTER(Data1, ISNUMBER(SEARCH(C2, Data1[company])), "No Matches")

Here’s what’s happening:

• Data1 is the table we’re searching.

• SEARCH(C2, Data1[Company]) looks for a match within the Company column.

• ISNUMBER ensures that only valid matches are returned.

• If there are no matches, it displays "No Matches."

This works well for a single dataset, but we need to extend it across multiple sheets.

Step 2: Combining Multiple Data Sources with VSTACK

To search across multiple sheets, we need to stack the filtered results. The VSTACK function helps us do just that by combining arrays vertically.

Here’s how we modify our formula to search across three sheets (Data1, Data2, and Data3):

=VSTACK(
    FILTER(Data1, ISNUMBER(SEARCH(C2, Data1[company])), ""),
    FILTER(Data2, ISNUMBER(SEARCH(C2, Data2[company])), ""),
    FILTER(Data3, ISNUMBER(SEARCH(C2, Data3[company])), "")
)

Breaking It Down:

• Each FILTER function extracts relevant rows from its respective dataset.

• VSTACK stacks the results from all three datasets into a single output.

• If no results are found in a dataset, an empty string ("") prevents errors.

Step 3: Implementing the Search Bar

Now that we have our function set up, we need to connect it to a search bar. Here’s how:

1. Select a cell (e.g., C2) where users will input their search term.

2. Place the VSTACK formula in a new sheet or column where you want the results to appear.

3. As users type in C2, the search results dynamically update.

Step 4: Enhancing the User Experience

Expanding the Search Scope

Instead of searching only by company name, you can modify the formula to search across multiple columns by using the BYROW function:

=VSTACK(
    FILTER(Data1, ISNUMBER(SEARCH(C2, Data1[company])) + ISNUMBER(SEARCH(C2, Data1[department])), ""),
    FILTER(Data2, ISNUMBER(SEARCH(C2, Data2[company])) + ISNUMBER(SEARCH(C2, Data2[department])), ""),
    FILTER(Data3, ISNUMBER(SEARCH(C2, Data3[company])) + ISNUMBER(SEARCH(C2, Data3[department])), "")
)

This modification allows users to search by both company name and location.

Handling Errors Gracefully

If the search term isn’t found, we can wrap the function with IFERROR to display a custom message:

=IFERROR(
    VSTACK(
        FILTER(Data1, ISNUMBER(SEARCH(C2, Data1[company])), ""),
        FILTER(Data2, ISNUMBER(SEARCH(C2, Data2[company])), ""),
        FILTER(Data3, ISNUMBER(SEARCH(C2, Data3[company])), "")
    ), "No matches found"
)

This prevents Excel from showing errors when no results are found.

Step 5: Final Touches and Optimization

• Sorting Results: Add SORT around the VSTACK function to organize results alphabetically or by ID.

• Using Drop-downs: Combine with Data Validation to allow users to pick from a list instead of typing manually.

• Expanding to More Tables: If you have additional datasets, simply add more FILTER functions inside VSTACK.

Real-World Applications

This setup is particularly useful when you're:

• Managing inventory across multiple warehouses

• Tracking customer data split across different departments

• Analyzing sales data from different regions

Troubleshooting

If you're getting errors, check that:

• Your table names match exactly (Data1, Data2, Data3)

• The column names are consistent across tables

• You've closed all parentheses in the formula

Conclusion

With just a few functions—FILTER, VSTACK, and SEARCH—you can create a powerful, dynamic search bar in Excel that works across multiple sheets. This method not only streamlines data retrieval but also improves user efficiency by consolidating results in real time.

Try implementing this in your own Excel projects, and let me know how it works for you!

Resources

• Copy and paste the formula:

=VSTACK(
   FILTER(Data1, ISNUMBER(SEARCH(C2, Data1[company]))),
   FILTER(Data2, ISNUMBER(SEARCH(C2, Data2[company]))),
   FILTER(Data3, ISNUMBER(SEARCH(C2, Data3[company])))
)

• Learn more about the dynamic search function: https://www.gotsheet.xyz/p/better-dynamic-search-bar

• Subscribe to my YouTube Channel: youtube.com/@eamonncottrell

NEXT STEPS

Whenever you’re ready, here’s how I can help:

1. Consulting (limited availability)
   Grab a slot on my calendar for help with a spreadsheet OR newsletter project.

2. Business tech stack (FREE)
   My recommendations for software and tools I use to create content and run my online business.

3. Sponsor Got Sheet
   Got Sheet teaches business operators, teachers and entrepreneurs how to get good at spreadsheets. Interested in sponsoring an issue or a series of Got Sheet newsletters? Reach out to get more information.

4. Personal budget tool
   As a Got Sheet subscriber, I want you to have a 75% discount on the personal budget I built 10+ years ago and am still using to this day.

5. YouTube

   If you aren’t subscribed yet, come on over to my YouTube channel where I make all my spreadsheet, coding and productivity tutorials

HAPPY SPREADSHEETING!

Enjoyed this issue?
Subscribe below or Leave a testimonial

Cheers, Eamonn
Connect on LinkedIn, YouTube & Twitter.