Excel’s Search and Find feature is very helpful when looking up things in workbooks, but this means searching for keywords one by one. SEARCH and FIND are formula versions of this very handy feature and can work wonders in large data sets.

Syntax

=ISNUMBER(SEARCH(text you look for, text to be checked))

Steps

  1. Begin by entering =ISNUMBER(
  2. Type in SEARCH( or FIND(
  3. Select or type in the range reference that includes the text you look for (i.e. $D$2)
  4. Select or type in the range reference that includes the text you want to check (i.e. $B5)
  5. Type in )) to close both functions and press Enter to complete the formula

How

Excel features 2 functions to search for a text in another: FIND and SEARCH. Both have the same structure. The first argument is the text to be searched, and the second is the text to be checked. They both return the index number for the first occurrence of the searched text. If no result is found, they both will return #VALUE! error. Essentially the logic is, if text is found, return number, if text is not found return error.

The ISNUMBER function comes in handy in here. This function returns TRUE or FALSE based on whether the argument parameter is a number. To get a TRUE/FALSE (Yes/No) in a text search, FIND/SEARCH function can be wrapped inside a ISNUMBER.

=ISNUMBER(SEARCH($D$2,$B5))

=ISNUMBER(FIND($D$2,$B5))

Both functions work pretty much the same except, for one difference: case-sensitivity. While FIND is case-sensitive, SEARCH is not. This means that SEARCH will not be able to return a result if the text found doesn’t give a perfect match with the search criteria. For example, SEARCH will return an error if you search for “Video” in the text below,

You can also type a keyword to search online for the video that best fits your document.