Function Problems

How to Search Excel Cells for Multiple Keywords in Any Order

John Wilson Oct 9, 2026 871 views

Question details

The user needs a method to search for multiple keywords within cells where words appear in random, comma-separated order, and then filter matching rows based on drop-down selections.

How to Search Excel Cells for Multiple Keywords in Any Order

Product: Excel
Device & OS: not provided
Scenario: Filtering spreadsheet data by selecting multiple keywords from a drop-down list to identify rows that contain the chosen words in any order.
Observed behavior: Finding the correct combination of functions (like FIND, SEARCH, TEXTSPLIT) and VBA scripts to accurately extract unique terms, allow multiple selections in a drop-down cell, and flag the matching rows.

Before you start

Ensure your version of Excel or WPS Office supports dynamic array formulas such as TEXTSPLIT and UNIQUE, as they are essential for extracting and filtering comma-delimited keywords efficiently.

Solution 1Recommended

Use Dynamic Array Formulas to Extract Keywords and Filter Data

This solution uses Excel's modern array formulas to extract unique keywords from your dataset into a drop-down list and uses a helper column to identify rows that match the selected keywords.

By utilizing functions like UNIQUE, TEXTSPLIT, and TEXTJOIN, you can create a dynamic list of keywords directly from your source data. The SEARCH function will allow for case-insensitive matching.

Extract Unique Keywords

Assuming your target text data is in cells H2:H100, select an empty cell (e.g., P2) and enter the formula: =LET(a,H2:H100,UNIQUE(TRANSPOSE(TRIM(TEXTSPLIT(TEXTJOIN(",",TRUE,a),"."))))). This generates a spill range of unique terms.

Create a Data Validation Drop-Down

Select the cells where you want to choose keywords (e.g., Q2:Q10). Navigate to the Data tab, click Data Validation, choose 'List' from the Allow drop-down, and enter '=$P$2#' as the Source.

Add a Helper Column for Matching

In cell R2, enter the formula =NOT(ISERROR(SUM(IF(Q2:Q10<>"",SEARCH(Q2:Q10,H2),0)))) to evaluate the matches. Copy this formula down alongside your data in column H.

Filter the Matching Rows

Select your keywords in the drop-down list in Q2:Q10. Then, apply a standard Data Filter to your headers and filter column R to only show rows with the value 'TRUE'.

Use Dynamic Array Formulas to Extract Keywords and Filter Data

Case Sensitivity: If you require the keyword search to be exactly case-sensitive, replace the SEARCH function with the FIND function in your helper column formula.

Solution 2

Enable Multiple Keyword Selections in a Single Cell Using VBA

If you prefer to select multiple keywords from a single drop-down cell (e.g., Q2) rather than multiple rows, you can use a VBA Worksheet event macro to append new selections.

Advanced Data Search and Filtering

Use WPS Spreadsheet to Manage Complex Data Queries

WPS Spreadsheet offers powerful data management features, full support for dynamic array functions, and complete compatibility with Microsoft Excel's VBA macros, making advanced keyword searching smooth and efficient.

1. Open Your Data: Launch WPS Spreadsheet and open your existing dataset containing the comma-separated terms.
2. Apply Array Formulas: Use built-in functions like TEXTSPLIT and SEARCH in the formula bar to isolate keywords and match cells dynamically.
3. Set Up Data Validation: Access the Data tab to create drop-down lists and seamlessly filter the matched keyword results in your helper column.

Fully supports advanced dynamic array functions like TEXTSPLIT and UNIQUE.100% compatible with Excel file formats (.xlsx) and macro-enabled workbooks (.xlsm).Built-in macro editor for automating tasks like multi-selection drop-downs.Free, lightweight, and user-friendly alternative to Microsoft Office.

Frequently Asked Questions

Can I make the keyword search case-insensitive?

Yes. Excel and WPS Spreadsheet provide two similar functions for finding text: FIND and SEARCH. The SEARCH function ignores text casing (case-insensitive), while the FIND function checks for exact case matches. Simply use SEARCH in your helper column formula.

Why is my TEXTSPLIT function returning a #NAME? error?

The TEXTSPLIT function is a newer dynamic array function. If you encounter a #NAME? error, it means your current version of the software does not support it. Consider updating to the latest version of WPS Office or Microsoft 365, or use a combination of MID, FIND, and LEN functions as a workaround.

How do I filter rows that contain ANY of the selected keywords instead of ALL?

To match rows containing at least one of the selected keywords instead of requiring all of them, modify the logic of your helper column. Instead of checking if the sum of matches equals a specific count, check if the SUM function's result is greater than 0.

Excel & Data Problems