Categorize Excel Comments by Matching Keywords in WPS Spreadsheets
Question details
The user needs a formula-driven way to inspect Excel cell comments and return the first category whose associated keywords appear in the comment text, or return blank when no keyword matches.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a list of comments and a separate keyword-to-category reference table.
- Observed behavior
- They want the matching category to appear automatically for each comment, with unmatched comments left blank.
Prepare the worksheet so the comments are in one column and the keyword reference table is in a stable range. Create one category per row with its associated search terms in one or two keyword columns before entering the formula.
Use an INDEX-MATCH-SEARCH array formula to return the first matching category
Use this solution when you have one or two keyword columns and want Excel-compatible categorization without helper columns or VBA.
The formula uses SEARCH to test each keyword range against the comment, ISNUMBER to convert matches to TRUE/FALSE, and MATCH(1,...) to find the first row with a hit. INDEX then returns the category, and IFERROR leaves the result blank when no keyword is found.
Place keywords in columns E and F and corresponding categories in column G. In this example, E3:E7 and F3:F7 hold search terms and G3:G7 holds categories.
Click the cell next to the first comment, such as B2, and enter =IFERROR(INDEX($G$3:$G$7,MATCH(1,ISNUMBER(SEARCH($E$3:$E$7,A2))+ISNUMBER(SEARCH($F$3:$F$7,A2)),0)),"").
In WPS Spreadsheets versions that do not use dynamic arrays automatically, press Ctrl+Shift+Enter instead of Enter to confirm the formula as an array formula.
Drag the fill handle down the result column to categorize the remaining comments.
Categorize Excel Comments by Keyword in WPS Spreadsheets
WPS Spreadsheets supports Excel-compatible functions such as INDEX, MATCH, ISNUMBER, and SEARCH, so you can build the same keyword-to-category formula without macros or add-ins.
- 1. Open WPS Spreadsheets: Launch WPS Office and open the workbook that contains the comment column and keyword reference table.
- 2. Arrange the reference table: Place keyword columns and a category column in a stable range, for example E3:E7 and F3:F7 for keywords and G3:G7 for categories.
- 3. Enter the formula: Select the first result cell and enter the INDEX/MATCH/SEARCH formula, then press Enter or Ctrl+Shift+Enter depending on your version.
- 4. Fill the formula down: Drag the fill handle down to apply the category lookup to all comments.
- 5. Review matched and blank cells: Check that matching comments show the correct category and non-matching comments remain blank.
Frequently Asked Questions
Which Excel versions support this keyword matching formula?
Microsoft 365, Excel 2021, and WPS Spreadsheets versions with dynamic arrays support it normally; older versions require Ctrl+Shift+Enter.
Can I use more than two keyword columns?
Yes. Add an extra ISNUMBER(SEARCH(range,comment)) term for each keyword column inside the MATCH function, and make sure the category range still corresponds row by row.
How do I leave a blank result when no keyword is found?
Wrap the formula in IFERROR with "" as the second argument. This returns an empty string when MATCH finds no matching keyword.
What should the keyword reference table look like?
Each row should represent one category with its search terms. In the example, columns E and F contain keywords and column G contains the category to return.
Does this formula distinguish uppercase and lowercase?
No. SEARCH is case-insensitive, so it matches keywords regardless of letter case in the comment text.
