Function Problems

Search All Excel Worksheets for Pattern-Matching Values

Algirdas JasaitisAlgirdas Jasaitis Oct 10, 2026 874 views

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.

Search All Excel Worksheets for Pattern-Matching Values
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the destination cell

Click on the cell in a summary sheet where you want the extracted list of unique values to appear.

2
Combine ranges with VSTACK

Enter the VSTACK function to combine your target ranges into one continuous array. For example: VSTACK(Sheet1!A2:A100, Sheet2!A2:A100).

3
Filter by character length

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).

4
Extract unique values and sort

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))).

5
Apply the formula

Press Enter to let the dynamic array spill the results down the column automatically.

Use Dynamic Array Formulas (VSTACK, FILTER, UNIQUE)
Adjusting for different patterns: Change the length criteria in the formula from 8 to 7 if you are looking for values with one letter followed by six digits.
Advanced Spreadsheets with WPS Office

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. 1. Open your multi-sheet workbook: Launch WPS Spreadsheet and open the workbook containing the multiple sheets you wish to search.
  2. 2. Select your output cell: Click on the specific cell where you want the pattern-matched, extracted list to display.
  3. 3. Input the array formula: Type your combined VSTACK, FILTER, and UNIQUE formula directly into the formula bar.
  4. 4. Generate the sorted list: Press Enter to execute the function and instantly generate a spilled array of your unique values.
Fully compatible with Microsoft Excel formulas, wildcards, and advanced array functions.Easily manage massive datasets across multiple worksheets without lagging or crashing.Lightweight, completely free to use, and features a highly familiar user interface.
microsoft office alternative - wps office

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.