We can use multiple search strings in a single SEARCH formula without nesting the function in Google Sheets.

Nested search involves using the SEARCH function in a nested way to incorporate more than one search string.

For example, a supplier has sent us the following items:

I want to test if either of the items “Apple” or “Banana” is available on the despatched list.

If the item is in cell B2, the formula for a single item would be:

=ISNUMBER(SEARCH("Apple", B2))

Note: To perform case-sensitive searches, replace the SEARCH function with the FIND function.

In the nested approach (which is not recommended), for searching the availability of two items, the formula would be:

=OR(
   ISNUMBER(SEARCH("Apple", B2)), 
   ISNUMBER(SEARCH("Banana", B2))
)

The OR logical function returns TRUE if either of the items is available. Replace it with AND if you want to return TRUE only when both items are available.

This type of nesting is not ideal for searching multiple search strings in a single search because if you want to search five strings, you would need five SEARCH functions nested.

What’s the alternative to the so-called nested SEARCH function?

I’ll come to that. But first, let me explain why I have used ISNUMBER with SEARCH in the example above.

The Role of the ISNUMBER Function with the SEARCH Function in Google Sheets

The SEARCH function in Google Sheets is used for searching a single search string. It returns not TRUE or FALSE, but the position at which the text string is first found within the text. When there is no match, the formula returns a #VALUE! error.

The ISNUMBER function will convert the error value(s) to Boolean FALSE and the matching position to TRUE.

A Single SEARCH Formula for Multiple Searches in Google Sheets

Let’s see how to use multiple search strings in a single SEARCH function in Google Sheets without nesting it.

If you look at the SEARCH function syntax, you’ll notice that there’s no provision to include multiple search strings (search_for).

Syntax:

SEARCH(search_for, text_to_search, [starting_at])

Note: starting_at is an optional argument.

There’s no search_for_1, search_for_2, etc., in the syntax. However, you can actually use multiple search texts within search_for. How?

Generic Formula:

ArrayFormula(SUM(--ISNUMBER(SEARCH({search_for_1, search_for_2, search_for_3, ... }, text_to_search))))

Using the fruit example, the nested SEARCH formula can be replaced by the following single SEARCH formula:

=ArrayFormula(SUM(--ISNUMBER(SEARCH({"Apple","Banana"}, B2))))

Finding If a Cell Contains One of Many Things FROM A RANGE

In the same example above, replace the search_for and text_to_search arguments with corresponding cell references.

Here, it’s important to note one thing: Empty cells in the search_for range can lead to unwanted outcomes.

For instance, if search_for is in C2:C3 and text_to_search is in B2, you should use the following formula:

=ArrayFormula(SUM(--ISNUMBER(SEARCH(TOCOL(C2:C3, 1), B2))))

The TOCOL function removes empty strings. This way you can find whether a cell contains one of many things in another range.

Multiple Search_For and Multiple Text_To_Search in a Single SEARCH Formula

When you have two ranges and want to search multiple strings in one range, you can use the MAP lambda function.

For example, if you want to search for the strings in C2:C4 within the range B6:B12 and return the availability in C6:C12, you first convert the SEARCH formula into a custom lambda function like this:

Search Formula:

ArrayFormula(SUM(--ISNUMBER(SEARCH(TOCOL(C2:C4, 1), B6:B12))))

Custom Lambda Function:

In the custom function, specify the search_for range (C2:C4) and replace the text_to_search range (B6:B12) with a meaningful name like ‘range’.

LAMBDA(range, ArrayFormula(SUM(--ISNUMBER(SEARCH(TOCOL(C2:C4, 1), range)))))

Now, use this custom function within the MAP function to search the text in C2:C4 in each element of the text_to_search range. Enter this formula in cell C6:

=MAP(B6:B12, LAMBDA(range, ArrayFormula(SUM(--ISNUMBER(SEARCH(TOCOL(C2:C4, 1), range))))))

This setup allows you to search multiple strings in one range and obtain results based on their availability in another range.

An Alternative Using REGEXMATCH Function

When you need to search for multiple strings in a single operation, consider using REGEXMATCH.

While our focus is on teaching advanced use of the SEARCH function, here’s an alternative approach to demonstrate the capabilities of Google Sheets.

In all the previous examples, the SEARCH formula (whether in array or non-array form) returns the count of matches. However, REGEXMATCH returns TRUE or FALSE based on whether there is a match or not.

For example, you can replace the earlier array formula in cell C6 with the following REGEXMATCH formula:

=ArrayFormula(REGEXMATCH(B6:B12, "(?i)"&TEXTJOIN("|", TRUE, C2:C4)))

This formula is case-insensitive. To make it case-sensitive, simply remove "(?i)"& from the formula.

Resources

• How to Create a Search Box Using Query in Google Sheets
• Search a Value and Sum up to That Row in Google Sheets
• Filter Rows if Search Key Present in Any Cell in that Rows in Google Sheets
• Logical AND, OR Use in SEARCH Function in Google Sheets
• Search a Value and Offset Cells in Google Sheets