Search All Excel Worksheets for Pattern-Matching Values
Question details
The user wants to search multiple worksheets to find and extract unique values that match a specific pattern, such as a 7-character code or an 8-digit serial number.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Extracting and filtering unique serial numbers from multiple sheets based on character length or specific text patterns.
- Observed behavior
- Needs a formula or method to aggregate data across multiple sheets and filter it into a single list of unique values without manual copying and pasting.
Ensure all the worksheets you want to search have the data structured in identical columns (e.g., all serial numbers in Column A) to make array stacking accurate.
Use Dynamic Array Formulas (VSTACK, FILTER, UNIQUE)
Combine ranges from multiple sheets and filter them by character length to extract unique matches automatically.
In modern versions of Excel (such as Microsoft 365) and WPS Office, dynamic array functions make it easy to manipulate data across multiple sheets. By nesting VSTACK inside FILTER and UNIQUE, you can consolidate the data and extract exactly what you need.
Click on the cell in a summary sheet where you want the extracted list of unique values to appear.
Enter the VSTACK function to combine your target ranges into one continuous array. For example: VSTACK(Sheet1!A2:A100, Sheet2!A2:A100).
Wrap the combined array in a FILTER function, using the LEN function to specify your pattern. For an 8-digit number, use: FILTER(VSTACK(...), LEN(VSTACK(...))=8).
Add the UNIQUE and SORT functions to the formula to output a clean, deduplicated list. The final formula will look like: =SORT(UNIQUE(FILTER(VSTACK(A2:A17,Sheet2!A2:A17),LEN(VSTACK(A2:A17,Sheet2!A2:A17))=8))).
Press Enter to let the dynamic array spill the results down the column automatically.

Use COUNTIF with Wildcards (To Count Matches)
If you are using an older version of Excel and only need to count matching patterns rather than extract them, use COUNTIF with wildcard characters.
Extract and Analyze Data Seamlessly in WPS Spreadsheet
WPS Spreadsheet fully supports modern dynamic array functions like UNIQUE, FILTER, and VSTACK, making it incredibly easy to search, consolidate, and manipulate complex data across multiple worksheets.
- 1. Open your multi-sheet workbook: Launch WPS Spreadsheet and open the workbook containing the multiple sheets you wish to search.
- 2. Select your output cell: Click on the specific cell where you want the pattern-matched, extracted list to display.
- 3. Input the array formula: Type your combined VSTACK, FILTER, and UNIQUE formula directly into the formula bar.
- 4. Generate the sorted list: Press Enter to execute the function and instantly generate a spilled array of your unique values.

Frequently Asked Questions
How do I extract values using wildcards instead of just text length?
While the FILTER function does not support wildcards directly, you can combine it with functions like SEARCH or ISNUMBER. For example, using FILTER(range, ISNUMBER(SEARCH("pattern", range))) allows you to extract values containing specific text snippets.
Why does my dynamic array formula return a #SPILL! error?
A #SPILL! error occurs when the dynamic array function does not have enough empty cells below or to the right to display all the results. You must clear any obstructing data or text in the spill range to fix the error.
Can I search across all worksheets dynamically without manually naming each sheet in the formula?
Yes, you can use a 3D reference with VSTACK in compatible versions. Entering a formula like =VSTACK(Sheet1:Sheet3!A2:A100) allows you to instantly stack the specified ranges from all consecutive sheets positioned between Sheet1 and Sheet3.




