Formula Errors

How to Fix Excel IFS with FIND or SEARCH Stopping at First Condition

Steven Parker Oct 15, 2026 870 views

Question details

The user wants to know why an IFS formula stops evaluating subsequent conditions when using FIND or SEARCH, and how to structure the formula to evaluate all conditions correctly.

How to Fix Excel IFS with FIND or SEARCH Stopping at the First Condition

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Evaluating multiple text search conditions sequentially in a single cell using the IFS function combined with FIND or SEARCH.
Observed behavior: The formula returns a #VALUE! error and stops evaluating subsequent conditions if the first search term is not found, because FIND and SEARCH return an error instead of a standard FALSE boolean.

Before you start

Before modifying your formula, determine whether your text search needs to be case-sensitive (use FIND) or case-insensitive (use SEARCH) based on your data.

Solution 1Recommended

Wrap FIND or SEARCH Functions with ISNUMBER

By enclosing the search function in ISNUMBER, any #VALUE! error is converted to FALSE, allowing the IFS function to evaluate the next condition smoothly.

The IFS function evaluates conditions from left to right and requires each condition test to return either TRUE or FALSE. When FIND or SEARCH fail to locate a substring, they generate a #VALUE! error rather than returning FALSE or zero. When IFS encounters this error on the very first condition, it immediately stops and outputs the error.

To resolve this, you must wrap each SEARCH or FIND function within the ISNUMBER function. If the text is found, SEARCH returns its numeric position, which ISNUMBER converts to TRUE. If the text is missing, SEARCH returns an error, which ISNUMBER converts to FALSE, telling IFS to move on to the next condition.

Select the target cell

Click on the cell where you want the final formula result to appear.

Start the IFS formula

Type =IFS( to begin your logical evaluation sequence.

Enter the first ISNUMBER condition

Type ISNUMBER(SEARCH("YourText", B1)), followed by a comma, and then type the value you want to return if this condition is met (e.g., "FoundText").

Complete the remaining conditions

Repeat the ISNUMBER(SEARCH(...)) logic for all your subsequent text checks, separating each pair with a comma. Finally, close the formula with a closing parenthesis and press Enter.

Wrap FIND or SEARCH Functions with ISNUMBER

Case Sensitivity Tip: Remember that SEARCH is case-insensitive, whereas FIND is case-sensitive. Use SEARCH for general matching to prevent accidental false negatives due to capitalization.

Efficient Data Processing

Easily Fix IFS and SEARCH Errors with WPS Spreadsheet

WPS Spreadsheet offers full compatibility with advanced Excel formulas like IFS, ISNUMBER, FIND, and SEARCH. You can seamlessly write, troubleshoot, and evaluate complex nested functions to automate your data analysis without formatting loss.

1. Open WPS Spreadsheet: Launch WPS Office and open your spreadsheet document.
2. Input the corrected formula: Select the target cell and enter the corrected formula using ISNUMBER and SEARCH combinations.
3. Evaluate the result: Press Enter to evaluate the result. WPS Spreadsheet handles the boolean logic perfectly, avoiding the #VALUE! calculation block.
4. Apply to multiple cells: Drag the fill handle downward to apply the fixed formula to the rest of your data column.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Clear error-checking tools to quickly spot formula issues.Lightweight, fast, and free to use for daily spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does FIND return an error instead of FALSE when text is missing?

In spreadsheet applications, the FIND and SEARCH functions are specifically designed to return the starting character position (a number) of the text. If the text is absent, they return a #VALUE! error to indicate the substring does not mathematically exist within the target string, rather than a logical FALSE.

Can I use IFERROR instead of ISNUMBER to fix the IFS function?

While IFERROR is excellent for catching errors at the end of a formula calculation, it is much harder to implement sequentially inside the condition checks of an IFS function. ISNUMBER is the most elegant approach because it evaluates the result directly into TRUE or FALSE, perfectly matching how IFS processes conditions.

What is the main difference between FIND and SEARCH in this formula?

The FIND function is strictly case-sensitive (meaning 'Check' does not match 'check'), whereas the SEARCH function is case-insensitive. For general text categorization, SEARCH is usually the safer choice unless you specifically need to verify text capitalization.

Excel & Data Problems