Fix an Excel IFS Formula Error Caused by SEARCH
Question details
The user needs an IFS formula to test multiple text criteria without returning an error when SEARCH finds no match.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A spreadsheet formula checks priority text in AB28 and a floor description in AC28 to return a corresponding value.
- Observed behavior
- SEARCH returns #VALUE! when it cannot find the specified text, causing the IFS formula to return an error.
Check that AB28 and AC28 contain the text you want to test, and adjust the cell references and search phrases if your worksheet uses different values. SEARCH is not case-sensitive, but spaces and punctuation in the search phrase must match the source text.
Wrap each SEARCH function in ISNUMBER
Use ISNUMBER to turn successful searches into TRUE and missing matches into FALSE before IFS evaluates the conditions.
Multiplying the two TRUE or FALSE results tests whether both text criteria are met. IFS returns the value associated with the first matching condition.
Click the cell containing the IFS formula, then click the formula bar to edit it.
Enter =IFS(ISNUMBER(SEARCH("HIGH",AB28))*ISNUMBER(SEARCH("Ground Beams/ Suspended in-situ Concrete Floor",AC28)),150,ISNUMBER(SEARCH("MEDIUM",AB28))*ISNUMBER(SEARCH("Ground Beams/ Suspended in-situ Concrete Floor",AC28)),100,TRUE,"No match") and press Enter.
Review the formula cell: it returns 150 for HIGH or 100 for MEDIUM when AC28 contains the specified floor description. If neither condition matches, it displays No match.

Fix SEARCH conditions in WPS Spreadsheets
Open your workbook in WPS Spreadsheets and use ISNUMBER with SEARCH to prevent missing text matches from causing an IFS formula error.
- 1. Open the workbook: Open WPS Spreadsheets, select Open, and choose your Excel workbook.
- 2. Edit the formula: Select the formula cell and use the formula bar to wrap each SEARCH expression in ISNUMBER.
- 3. Add a fallback: In the IFS formula, add a final TRUE condition and a result such as "No match", then press Enter.
- 4. Save the workbook: Check the result and select Save or Save As to keep the updated workbook.

Frequently Asked Questions
Why does SEARCH return #VALUE! inside IFS?
SEARCH returns #VALUE! when it cannot find the specified text. Without an error-handling test, that error can propagate into the IFS result.
How does ISNUMBER prevent the SEARCH error?
Use ISNUMBER(SEARCH("HIGH",AB28)). A successful SEARCH returns a numeric position, so ISNUMBER returns TRUE; a missing match returns FALSE.
Why does my corrected IFS formula return #N/A?
IFS returns #N/A when none of its conditions is true and no fallback is provided. Add a final condition and result, such as TRUE,"No match", before the closing parenthesis.
Does SEARCH distinguish between uppercase and lowercase text?
No. SEARCH is case-insensitive, so searching for HIGH also finds high. Spaces and punctuation in the search phrase still need to match the text being searched.




