Function Problems

Search for Multiple Words in Excel and Return All Matches

Chanuka GeekiyanageChanuka Geekiyanage Nov 18, 2026 868 views

Question details

Find up to 10,000 words or phrases in Excel cells and return every matching search term in an adjacent cell.

Search for Multiple Words in Excel and Return All Matches
Product
Microsoft Excel for Microsoft 365
Device & OS
not provided
Scenario
Search a large dataset without Power Query, ignoring case and separating multiple matches with commas.
Observed behavior
Each result cell should contain the matching terms from the search list, separated by commas.
Before you start

Place the text to search in column A and the search terms in F2:F10001. Use a version of Excel that supports FILTER and TEXTJOIN, such as Excel for Microsoft 365.

Solution 1Recommended

Use FILTER and TEXTJOIN to return all matching terms

Use this formula to search each cell against a list of up to 10,000 terms and return the matches in one cell.

SEARCH ignores case and matches text anywhere within the cell, including inside longer words. The results use the spelling and capitalization from the search list; duplicate terms in that list can appear more than once.

Checking 10,000 terms for every source cell can slow recalculation on large datasets. Limit the search range to the terms you need when possible.

1
Enter the search terms

Type or paste the words and phrases to find into cells F2:F10001. Adjust the range if your list is shorter.

2
Enter the formula

Select B2 and enter =TEXTJOIN(", ",TRUE,FILTER($F$2:$F$10001,($F$2:$F$10001<>"")*ISNUMBER(SEARCH($F$2:$F$10001,A2)),""))

3
Fill the formula down

Drag the fill handle from B2 down alongside the text in column A. Each result cell will show matching terms separated by commas, or remain blank if none match.

Use FILTER and TEXTJOIN to return all matching terms
Substring matching: A search for "cat" also matches "catch". Whole-word matching requires a different approach.
Search text in WPS Spreadsheet

Find and combine matching terms in WPS Spreadsheet

Use the same SEARCH, FILTER, and TEXTJOIN approach in a WPS Spreadsheet version that supports these functions. The formula returns matching search-list entries in a comma-separated cell.

  1. 1. Open the workbook: Open WPS Spreadsheet and choose Open to select your Excel workbook.
  2. 2. Set up the search list: Place the source text in column A and enter the search terms in F2:F10001.
  3. 3. Enter the formula: Select B2 and enter =TEXTJOIN(", ",TRUE,FILTER($F$2:$F$10001,($F$2:$F$10001<>"")*ISNUMBER(SEARCH($F$2:$F$10001,A2)),""))
  4. 4. Fill down and save: Drag B2's fill handle down to apply the formula, then use File > Save or Save As to save the workbook.
Search for multiple words or phrases without case sensitivity.Combine matching terms in an adjacent cell.Open and save Microsoft Excel XLSX files.Use a familiar spreadsheet interface.
microsoft office alternative - wps office

Frequently Asked Questions

Is the search case-sensitive?

No. SEARCH ignores case, so a term such as "apple" matches "Apple" in the source cell. The returned term retains the capitalization used in the search list.

Does the formula match whole words only?

No. SEARCH finds substrings, so "cat" also matches "catch". Use a different whole-word matching approach if that is not suitable.

What happens when no terms match?

The FILTER formula supplies an empty string when there are no matches, so the result cell appears blank.

Why is the workbook recalculating slowly?

Each result formula checks every term in the specified range. Reduce F2:F10001 to the actual search-list range or apply the formula to fewer source rows.

Can I use this formula in an older version of Excel?

The formula requires FILTER and TEXTJOIN. Check whether your Excel version supports both functions; versions without FILTER need a different approach.