Function Problems

How to Return Multiple Values with Wildcard Matching in Excel

WPS Content Manager Oct 7, 2026 871 views

Question details

The user needs a method to extract and return multiple matching values from a dynamic lookup array based on partial or wildcard text matches.

How to Return Multiple Values with Wildcard Matching in Excel

Product: Excel
Device & OS: not provided
Scenario: Searching a dataset using a partial keyword and attempting to output all corresponding matches from a return array, rather than just the first matched item.
Observed behavior: Standard lookup formulas like XLOOKUP or VLOOKUP only return the first matched value and stop searching. A dynamic array formula approach is required to retrieve all occurrences.

Before you start

Ensure you are using Microsoft 365, Office 2021, or a modern spreadsheet editor like WPS Office, as the dynamic FILTER function required for this solution is not supported in Excel 2019 or earlier versions.

Solution 1Recommended

Use FILTER and SEARCH Functions to Extract Multiple Matches

Combine the FILTER and SEARCH functions to find all instances of a partial text match and spill the results into a dynamic array.

While VLOOKUP and XLOOKUP support wildcard characters (like '*' and '?'), they are structurally designed to return only the first result they encounter. To generate a dynamic list of all matching items, you must use the FILTER function.

By nesting the SEARCH function inside ISNUMBER, you create a TRUE/FALSE logic array that FILTER uses to extract every row containing your specific search string.

Identify your lookup value and arrays

Determine the cell containing your partial search term (e.g., A1), your lookup array where the text will be searched (e.g., B2:B20), and the return array from which you want to pull the data (e.g., C2:C20).

Construct the partial match logic

In an empty cell, type the SEARCH formula wrapped in ISNUMBER to find partial matches regardless of case: ISNUMBER(SEARCH(A1, B2:B20)). This creates a boolean array determining which cells contain the search term.

Apply the FILTER function

Wrap the previous logic inside the FILTER function to extract the actual values: =FILTER(C2:C20, ISNUMBER(SEARCH(A1, B2:B20))).

Transpose the results (Optional)

If you need the multiple returned values to display horizontally across columns instead of spilling vertically down rows, enclose the entire formula in a TRANSPOSE function: =TRANSPOSE(FILTER(C2:C20, ISNUMBER(SEARCH(A1, B2:B20)))).

Use FILTER and SEARCH Functions to Extract Multiple Matches

Handling Empty Results: To prevent a #CALC! error when no records match your search term, add a third argument to the FILTER function: =FILTER(C2:C20, ISNUMBER(SEARCH(A1, B2:B20)), "No Match Found").

Powerful Spreadsheet Editor

Effortlessly Manage Dynamic Arrays with WPS Office

WPS Spreadsheet fully supports advanced dynamic array formulas, including FILTER, XLOOKUP, and SEARCH. You can easily extract multiple matching values and manage complex datasets with its highly compatible and intuitive interface.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing the data you want to search.
2. Enter the formula: Select an empty cell with enough adjacent space for the results to spill, and type your =FILTER( logic.
3. Apply the search condition: Highlight your target arrays and input the ISNUMBER(SEARCH(...)) condition. Press Enter to instantly populate the multiple results.

Seamlessly compatible with Microsoft Excel formulas and file formats (.xlsx, .xls).Native support for modern dynamic array functions like FILTER, UNIQUE, and XLOOKUP.Lightweight application size with rapid startup times.Free to use with a familiar tabbed user interface, minimizing the learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my XLOOKUP wildcard search only return one value?

XLOOKUP and VLOOKUP are traditional lookup functions designed to find the first exact or approximate match in a given array. Once they identify the first valid instance, they stop searching, which is why they cannot spill multiple matched values into adjacent cells.

Do I need to use wildcard characters like * or ? inside the SEARCH function?

No, the SEARCH function inherently looks for the specified text string anywhere within the target cell. Because it functions as a "contains" search, manually adding asterisk (*) wildcards is unnecessary and redundant.

Why am I getting a #CALC! error with my FILTER formula?

A #CALC! error typically occurs in dynamic array functions when the provided filter conditions result in an empty array (i.e., no records match the criteria). You can resolve this by adding the optional [if_empty] argument at the end of your FILTER formula, such as "No results".

Does this multiple value lookup formula work in older versions of Excel?

No. The FILTER function relies on the dynamic arrays calculation engine, which was introduced in Microsoft 365 and Office 2021. If you are using Excel 2019 or earlier, you would need to rely on complex legacy array formulas utilizing INDEX and AGGREGATE.

Excel & Data Problems