WPS Spreadsheets: Fix SEARCH #VALUE! in Excel Table References
Question details
The user needs to search for text in an Excel table column with a structured reference and prevent a #VALUE! error in a calculated column.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A calculated Excel table column uses SEARCH to find the word "security" in the Labels column and return a Boolean or text result.
- Observed behavior
- SEARCH returns #VALUE! when its third argument, start_num, is set to 0. SEARCH also returns an error when the search text is not found unless the formula handles that condition.
Select the calculated table column and inspect the formula bar to confirm the structured reference points to the Labels column. Check whether the SEARCH formula includes a third argument set to 0.
Remove the zero SEARCH start position and test the structured reference
Use this solution when SEARCH should begin at the first character of each Labels cell and the formula needs a TRUE or FALSE result.
The SEARCH start_num argument must be 1 or greater. When the search should begin at the first character, omit the third argument instead of entering 0. ISNUMBER converts a successful match into TRUE and a missing match into FALSE without displaying #VALUE!.
Open the worksheet in WPS Spreadsheets, click a cell in the table's calculated column, and select the formula in the formula bar.
Enter =ISNUMBER(SEARCH("security",[@Labels])) in the calculated column, then press Enter. WPS Spreadsheets should fill the formula through the table column.
To display Y for a match and a blank for a non-match, enter =IF(ISNUMBER(SEARCH("security",[@Labels])),"Y","") in the calculated column.
Use COUNTIF with a wildcard for a simple text match
Use this alternative when you only need to check whether the Labels cell contains the target word and do not need SEARCH's character-position behavior.
Use WPS Spreadsheets to Fix SEARCH Errors in Table Formulas
WPS Spreadsheets lets you edit Excel-compatible table formulas, remove the invalid zero start position, and use SEARCH, ISNUMBER, IF, or COUNTIF to create reliable calculated-column results.
- 1. Open the workbook: Launch WPS Spreadsheets and open the Excel workbook containing the table.
- 2. Locate the calculated column: Click a formula cell in the table column that searches the Labels field and review its formula in the formula bar.
- 3. Enter the corrected formula: Replace the formula with =ISNUMBER(SEARCH("security",[@Labels])) and press Enter.
- 4. Check the table results: Confirm that matching rows show TRUE and rows without the word show FALSE, with no #VALUE! error.
Frequently Asked Questions
Why does SEARCH return #VALUE! when start_num is 0?
The SEARCH start_num argument must be at least 1. Remove the third argument when searching from the beginning, or change it to 1 or a higher value.
How can I search the current Excel table row with a structured reference?
Use a row reference such as =ISNUMBER(SEARCH("security",[@Labels])) in the calculated column. The [@Labels] reference points to the Labels cell in the current row.
How do I prevent an error when the text is not found?
Wrap SEARCH in ISNUMBER, as in =ISNUMBER(SEARCH("security",[@Labels])). This returns FALSE when SEARCH cannot find the text instead of displaying #VALUE!.
Can I return Y or a blank instead of TRUE or FALSE?
Yes. Use =IF(ISNUMBER(SEARCH("security",[@Labels])),"Y","") in the table's calculated column.
What is an alternative to SEARCH for a simple contains check?
Use =COUNTIF([@Labels],"*security*") for a numeric match result, or add >0 to return TRUE or FALSE. The asterisks match any text before or after security.
