Excel IFERROR with Search: How to Use It

Formulas & functions · Updated November 22, 2026

The direct answer: wrap a SEARCH (or FIND) function in IFERROR to return a custom message or blank cell when the search text isn’t found. For example, =IFERROR(SEARCH("apple", A1), "Not found") returns the position of “apple” in A1 if present, or “Not found” if absent.

What IFERROR and SEARCH Each Do

IFERROR is a function that takes two arguments: a value or formula to evaluate, and a value to return if the first argument results in an error. It catches all Excel error types—#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL!—and replaces them with whatever you specify.

SEARCH finds the starting position of a text string within another text string, counting from 1. It is case-insensitive and supports wildcards (* for any sequence, ? for a single character). If the text isn’t found, SEARCH returns #VALUE!, which is exactly the error IFERROR can handle.

There’s also FIND, which is case-sensitive and does not allow wildcards. Both return the same #VALUE! error when no match is found, so the same IFERROR wrapper works for either.

Basic Formula Examples

Assume cell A1 contains the text “Excel IFERROR search function”.

  • =IFERROR(SEARCH("error", A1), "Not found") returns 7 (the position where “error” starts).
  • =IFERROR(SEARCH("missing", A1), "Not found") returns Not found.
  • =IFERROR(SEARCH("e", A1), "") returns 1 because SEARCH is case-insensitive and finds the first “e” at position 1. To be case-sensitive, use =IFERROR(FIND("e", A1), "") which returns 6 (the first lowercase “e”).

If you want to return a blank cell instead of a message, use empty quotes: =IFERROR(SEARCH("x", A1), "").

Using IFERROR with SEARCH in Real Scenarios

One common use is categorizing data based on keywords. Suppose you have product descriptions in column A and you want to mark rows that mention “sale”. In B2, enter:

=IF(ISNUMBER(SEARCH("sale", A2)), "Yes", "No")

This works because ISNUMBER converts the result of SEARCH into TRUE if a number is returned, FALSE if an error. You can also use IFERROR directly: =IF(IFERROR(SEARCH("sale", A2), 0)>0, "Yes", "No").

Another scenario is extracting a substring after a keyword. If A1 contains “Order: 12345”, you can extract “12345” with:

=IFERROR(MID(A1, SEARCH(":", A1)+1, 10), "No colon")

Here, SEARCH finds the colon, and MID pulls the next 10 characters. If no colon exists, SEARCH errors and IFERROR returns “No colon”.

Choosing Between IFERROR, IFNA, and IF(ISERROR)

IFERROR catches all error types, which is convenient but can mask unexpected errors. For instance, if you mistype a function name, IFERROR will hide that #NAME? error and return your custom message, making debugging harder.

IFNA (available in Excel 2013 and later) catches only #N/A errors. Since SEARCH returns #VALUE!, IFNA won’t help here—you’d need IFERROR. But if you use VLOOKUP or XLOOKUP, IFNA is often better because it only handles the “not found” case.

The older =IF(ISERROR(SEARCH(...)), "not found", SEARCH(...)) requires calling SEARCH twice, which is inefficient and harder to read. IFERROR is the modern, cleaner choice.

Common Mistakes and How to Avoid Them

  • Forgetting that SEARCH is case-insensitive: If you need case-sensitive matching, use FIND instead.
  • Using IFERROR with SEARCH but expecting a boolean: SEARCH returns a position number, not TRUE/FALSE. Use ISNUMBER or compare to 0 as shown above.
  • Not accounting for wildcards: If your search text contains an asterisk or question mark, SEARCH treats them as wildcards. To search for a literal asterisk, use ~* (tilde before the character).
  • Returning text that looks like a number: If you return “0” as a string, it won’t be treated as a number in further calculations. Use 0 without quotes if you need a numeric fallback.

IFERROR with SEARCH in Array Formulas

In Excel 365, you can use SEARCH with dynamic arrays. For example, to check if any cell in A2:A10 contains “urgent”, you can use:

=IFERROR(SEARCH("urgent", A2:A10), "")

This returns an array of positions or empty strings for each cell. However, if you want a single TRUE/FALSE per row, you’d use =ISNUMBER(SEARCH("urgent", A2:A10)) which returns an array of TRUE/FALSE values.

For a single overall check, use =IF(COUNT(SEARCH("urgent", A2:A10))>0, "Found", "Not found"). Note that SEARCH with a range returns an array; COUNT counts numeric values, so if any position is found, the count is >0.

Frequently Asked Questions

Why does SEARCH return #VALUE! instead of 0 when not found? Because Excel uses #VALUE! to indicate that the function couldn’t find the text. That’s why IFERROR is needed—it catches that specific error.

Can I use IFERROR with SEARCH to check if a cell contains any of several words? Yes, but you’d need to nest multiple SEARCH functions. For example: =IF(OR(ISNUMBER(SEARCH("cat", A1)), ISNUMBER(SEARCH("dog", A1))), "Yes", "No"). Alternatively, use SUMPRODUCT for a list of words.

Does IFERROR slow down my workbook? Not noticeably for typical use. However, if you have thousands of formulas with SEARCH, consider using ISNUMBER alone to avoid the overhead of IFERROR’s error handling.

Is there a difference between SEARCH and FIND in terms of wildcards? Yes. SEARCH supports wildcards (*, ?), FIND does not. Also, FIND is case-sensitive, SEARCH is not.

Can I use IFERROR with SEARCH across multiple sheets? Yes, just reference the other sheet in the SEARCH argument, e.g., =IFERROR(SEARCH("x", Sheet2!A1), "").

Alternatives to IFERROR with SEARCH

If you’re using Excel 365, you might prefer TEXTBEFORE or TEXTAFTER for extracting text around a delimiter, but they don’t directly tell you if a substring exists. For existence checks, ISNUMBER(SEARCH(...)) is simpler than wrapping in IFERROR.

For more complex pattern matching, you can use REGEXTEST (available in Excel 365 via the REGEX functions) but that’s a separate feature. The classic combination of IFERROR and SEARCH remains the most straightforward way to handle “find text, or return a fallback”.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.