Find Text Matches in WPS Spreadsheets Cells with Many Possible Values
Question details
The user needs to search a cell against a separate list of possible text values and return the matching item without using deeply nested IF statements.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has target cells and a separate range of search terms, and wants to identify which term appears in each cell.
- Observed behavior
- The desired result is a dynamic range-based formula that returns the matching list item or blank without maintaining deeply nested IF statements.
Place all possible search terms in one continuous range, such as R2:R100, and confirm that the target cells contain text. Make sure the result column is empty so the formula can be filled down without overwriting data.
Return the matching text with INDEX, MATCH, and SEARCH
Use this array formula to check the target cell against the search term range and return the first matching item, or a blank when no match exists.
The SEARCH function tests each term in the range against the target cell and returns the starting position if found. ISNUMBER converts those results to TRUE/FALSE, MATCH finds the first TRUE, and INDEX returns the corresponding item. IFERROR handles cells with no match.
Click the cell where you want the matching text to appear, for example I2 next to the first target value in H2.
In the formula bar, type =IFERROR(INDEX($R$2:$R$100,MATCH(TRUE,ISNUMBER(SEARCH($R$2:$R$100,H2)),0)),""). Replace $R$2:$R$100 with your search term range and H2 with the first cell to test.
Press Enter in current WPS Spreadsheets or Microsoft 365/Excel 2021. In older versions, press Ctrl+Shift+Enter to enter it as an array formula.
Drag the fill handle from the result cell downward to copy the formula to the remaining cells.
Match Text Against a List in WPS Spreadsheets Without Nested IFs
WPS Spreadsheets can directly perform this range-based text match using the same INDEX, MATCH, and SEARCH formula as Excel. You can store all possible values in a separate list, make changes without rewriting nested IF statements, and keep your workbook compatible with XLSX files.
- 1. Open WPS Spreadsheets: Launch WPS Office and open the workbook that contains the cells and the search term list.
- 2. Prepare the search term range: Place all possible text values in one column, such as R2:R100, and keep the target values in a data column such as H.
- 3. Enter the matching formula: In the result cell, enter =IFERROR(INDEX($R$2:$R$100,MATCH(TRUE,ISNUMBER(SEARCH($R$2:$R$100,H2)),0)),"").
- 4. Confirm and fill down: Press Enter in current WPS Spreadsheets, or Ctrl+Shift+Enter in older versions, then drag the fill handle to apply the formula to the remaining rows.
Frequently Asked Questions
Does this formula work in older versions of WPS Spreadsheets?
Yes. In older versions that do not support dynamic arrays, enter the formula and press Ctrl+Shift+Enter instead of Enter. Current WPS Spreadsheets and Microsoft 365/Excel 2021 usually accept a normal Enter.
What happens if no text in the search list matches the cell?
The IFERROR function returns an empty string, so the result cell remains blank. You can replace the empty string with a custom value such as "No match" by changing the last argument of IFERROR.
Can I make the search case-sensitive?
Yes. Replace SEARCH with FIND in the formula. FIND treats uppercase and lowercase letters as different, while SEARCH is case-insensitive.
How do I use a search list that is located on another worksheet?
Use a qualified range such as Sheet2!$A$2:$A$100 in place of $R$2:$R$100. If the sheet name contains spaces, wrap it in single quotes, for example 'My Terms'!$A$2:$A$100.
