Return a Value When an Excel Cell Contains Any Word From a List
Question details
The user needs to check whether a text cell contains any word from a separate list and return "Groceries" when a match is found.
- Product
- Microsoft Excel 2013
- Device & OS
- not provided
- Scenario
- Text is entered in cell A2, while the words to search for are listed in cells D2:D4.
- Observed behavior
- The desired result is "Groceries" when A2 contains at least one listed word; otherwise, the formula should return "Nil".
Place the text to evaluate in A2 and keep each search word in a separate cell in D2:D4. Make sure the list does not include unintended blank cells or partial terms that could produce unwanted matches.
Use an array formula to test every word in the Excel list
Use this method in Excel 2013 and other Excel versions that require legacy array formulas for this type of multi-item text check.
The COUNTIF portion tests A2 against each wildcard search criterion created from D2:D4. The OR function returns TRUE when any item in the list is found.
Select the cell where you want the result, such as B2, and enter: =IF(OR(COUNTIF(A2,"*"&$D$2:$D$4&"*")),"Groceries","Nil")
Instead of pressing Enter alone, press Ctrl+Shift+Enter. Excel adds curly braces around the formula in the formula bar to indicate that it is an array formula.
Type text containing one of the words in D2:D4 into A2. The result cell returns "Groceries" when a match exists and "Nil" when no listed word is found.
Use SUMPRODUCT when you do not want to press Ctrl+Shift+Enter
Use this alternative when you want a standard formula entry method while still checking A2 against multiple words.
Adjust the formula for a larger search-word range
Use this option when your word list includes more than the three cells D2:D4.
Check a WPS Spreadsheet Cell for Any Word in a List
WPS Spreadsheets lets you build the same text-matching formula to classify entries such as grocery items. Open an Excel workbook, place your text and word list in the appropriate cells, then use a compatible COUNTIF-based formula to return a custom value when any list item is found.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open your .xlsx or .xls file.
- 2. Set up the text and word list: Enter the text to check in A2 and place each search word in cells D2:D4.
- 3. Enter the matching formula: Select B2 and enter =IF(SUMPRODUCT(COUNTIF(A2,"*"&$D$2:$D$4&"*"))>0,"Groceries","Nil").
- 4. Fill the formula for additional rows: Select B2 and drag the fill handle downward to apply the same word-list check to other text cells.
Frequently Asked Questions
Why does Excel require Ctrl+Shift+Enter for this formula?
In legacy Excel versions, COUNTIF must evaluate multiple criteria from D2:D4 as an array. Pressing Ctrl+Shift+Enter confirms the formula as an array formula so every list item is tested.
Can I use more than three words in the search list?
Yes. Replace $D$2:$D$4 with the range containing all of your words, such as $D$2:$D$50. Keep the dollar signs if you plan to copy the formula down.
How can I avoid matching part of another word?
The wildcard formula matches text anywhere in A2, so it can match partial words. For exact whole-word matching, normalize the text and use delimiter-based logic or separate the words into individual cells before comparing them.
What does the formula return when none of the listed words appears in A2?
It returns "Nil" because the IF function uses that value when no COUNTIF result is found. You can replace "Nil" with any result you prefer, such as an empty string ("").
Will this formula work in WPS Spreadsheets?
Yes. WPS Spreadsheets supports Excel-compatible IF, COUNTIF, OR, and SUMPRODUCT formulas and can open .xlsx and .xls workbooks.
