How to Search Partial Text and Return Multiple Rows in Excel
Question details
The user needs a formula to perform a partial text search and return all matching rows instead of just the first match.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Extracting multiple records from a dataset based on a partial keyword match.
- Observed behavior
- Using standard lookup functions like XLOOKUP or VLOOKUP only returns a single match. The goal is to dynamically return every row containing the specified partial string.
Ensure you are using a version of Excel or WPS Office that supports Dynamic Array formulas, specifically the FILTER function, as older versions do not support this capability natively.
Use FILTER and SEARCH for Case-Insensitive Matching
Combine the FILTER, ISNUMBER, and SEARCH functions to extract all rows that contain a specific text snippet, regardless of capitalization.
While XLOOKUP is great for finding a single result, the FILTER function combined with SEARCH evaluates the entire array and spills all positive matches into multiple rows. The SEARCH function is case-insensitive, making it ideal for general keyword lookups.
Select a designated cell, such as E1, and type the partial text you want to search for.
Click the cell where you want the extracted rows to appear and enter the formula: =FILTER(A:A,ISNUMBER(SEARCH(E1,A:A)),"No match found").
To return multiple columns of data alongside the matched text, expand the first array argument. For example, use =FILTER(A:D,ISNUMBER(SEARCH(E1,A:A)),"No match found").
Press Enter. The formula will spill down and automatically display all rows containing the partial text.
Use FILTER and FIND for Case-Sensitive Matching
If exact capitalization is required for your search, replace the SEARCH function with the FIND function.
Easily Filter Partial Text with WPS Spreadsheet
WPS Office fully supports dynamic array functions like FILTER, SEARCH, and FIND. You can easily extract multiple matching rows using the exact same formulas as Excel, completely for free.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your dataset.
- 2. Enter your keyword: Type your target search keyword into a blank cell, like E1.
- 3. Apply the FILTER formula: Enter =FILTER(A:D,ISNUMBER(SEARCH(E1,A:A))) into the results cell and press Enter to instantly see all matching rows.
Frequently Asked Questions
Why does my FILTER formula return a #CALC! error?
The #CALC! error usually occurs when the FILTER function finds no matching records and the [if_empty] argument is not provided. Add "No match found" at the end of your formula to handle this gracefully.
Can I use XLOOKUP to return multiple rows based on partial text?
No, XLOOKUP is fundamentally designed to return the first matching instance it finds. For extracting multiple rows, you must use the FILTER function combined with array evaluations like ISNUMBER(SEARCH()).
How do I search for partial text across multiple columns at once?
You can combine multiple SEARCH conditions using the addition (+) operator for OR logic within the FILTER function. For example: =FILTER(A:D,ISNUMBER(SEARCH(E1,A:A))+ISNUMBER(SEARCH(E1,B:B))).
