How to Filter a Large Excel Table by Multiple Keywords
Question details
The user needs to filter a large data table based on multiple keywords in a specific column using either AND or OR logic, and return only selected columns.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Filtering a large dataset dynamically using a list of keyword criteria defined in a separate table.
- Observed behavior
- The user wants to extract specific columns where the description matches any (OR) or all (AND) of the provided keywords using modern array formulas.
Ensure your version of Excel supports dynamic arrays and the new REGEXTEST function if you plan to use regular expressions. Verify that your criteria list is organized in a structured table for easy formula referencing.
Filter Table Using OR Criteria (Match Any Keyword)
Use this method when you want to return rows where the target column contains at least one of the keywords from your criteria list.
This solution utilizes the REGEXTEST function to check for the presence of keywords. By joining your criteria list with a pipe character, you create an OR operator for the regular expression.
Create a table or named range (e.g., Table4[Criteria]) containing all the keywords you want to search for in your dataset.
Use the TEXTJOIN function to create a single regular expression pattern separated by the OR operator: TEXTJOIN("|",,Table4[Criteria]).
Write the formula to filter rows where the description matches any keyword: =FILTER(Table3, REGEXTEST(Table3[Description], TEXTJOIN("|",,Table4[Criteria]))).
Wrap your formula in CHOOSECOLS to extract only the specific columns you need. For example: =CHOOSECOLS(FILTER(...), 1, 2, -1).
Filter Table Using AND Criteria (Match All Keywords)
Use this method when a row must contain every single keyword in your criteria list to be included in the results.
Handle Large Datasets Effortlessly with WPS Office
While advanced Power Query and REGEXTEST formulas are specific to the latest Microsoft Excel updates, WPS Office offers a free, lightweight, and highly compatible alternative. It supports standard array functions, robust filtering tools, and processes large datasets with a familiar interface.
Frequently Asked Questions
What is the difference between AND and OR criteria when filtering?
OR criteria will return a row if it matches at least one of the specified keywords. AND criteria strictly require all specified keywords to be present in the target cell for the row to be returned in the filtered array.
Why is my REGEXTEST function returning a #NAME? error?
The REGEXTEST function is a newer feature introduced in recent Excel updates. If it returns a #NAME? error, your current version of the software does not support regular expression functions yet. You may need to use traditional ISNUMBER(SEARCH()) array combinations instead.
How do I filter a table returning only specific columns instead of the entire table?
You can wrap your FILTER formula inside the CHOOSECOLS function. By specifying the column index numbers (e.g., 1, 2, -1 for the first, second, and last columns), CHOOSECOLS will output only those designated columns from your filtered dataset.
Can I use wildcard characters for multiple keywords without REGEXTEST?
While standard filter menus support wildcards (* and ?), applying them to a dynamic array of multiple keywords in a formula usually requires combining them with functions like SEARCH, ISNUMBER, and MMULT to simulate OR/AND logic.
