Excel: Use SEARCH Function to Search Multiple Values

by Tutor Aspire January 17, 2023

The SEARCH function in Excel can be used to find the location of one text string within another.

However, sometimes you may wish to use the SEARCH function to search for the existence of one of several text strings within another.

You can use the following formula to do so:

=SUMPRODUCT(--ISNUMBER(SEARCH({"string1","string2","string3"},A2)))>0

This particular formula searches for “string1”, “string2”, and “string3” within the string in cell A2.

If any of these strings are found, the formula returns TRUE.

Otherwise, it returns FALSE.

The following example shows how to use this formula in practice.

Example: Use SEARCH Function to Search Multiple Values in Excel

Suppose we have the following list of basketball team names in Excel:

Now suppose we would like to determine if the team name “Pacers”, “Raptors”, or “Nuggets” exists in each row.

We can use the following formula to do so:

=SUMPRODUCT(--ISNUMBER(SEARCH({"Pacers","Raptors","Nuggets"},A2)))>0

We can type this formula into cell B2 and then click and drag this formula down to each remaining cell in column B:

Column B displays TRUE if the team name contains one of the three strings we specified.

Otherwise, it returns FALSE.

If you would instead like to return 1 or 0 instead of TRUE and FALSE, you can use the following formula:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH({"Pacers","Raptors","Nuggets"},A2)))>0,1,0)

The following screenshot shows how to use this formula in practice:

Column B displays 1 if the team name contains one of the three strings we specified.

Otherwise, it returns 0.

The following tutorials explain how to perform other common tasks in Excel: