Excel: Use SEARCH Function to Search Multiple Values


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.

Additional Resources

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

Excel: A Formula for LEFT Until Specific Character
Excel: How to Use MID Function to End of String
Excel: How to Use MID Function for Variable Length Strings

4 Replies to “Excel: Use SEARCH Function to Search Multiple Values”

  1. Hello,

    Very good explanation.

    Question.
    The function doesn’t seem to work, can’t even be saved if the hardcoded search list {“Pacers”,”Raptors”,”Nuggets”} is replaced with a reference to a cell that contains the string ?

    Isn’t this supported ?
    If yes, could you explain how we could use a cell reference instead of a hardcoded list ?
    Kr

  2. I had spreadsheet where i need to match teachers post code, subject and age phase to the students, i did this with textjoin, then had to you text split to separate the text names in order to look for the name of a teacher that was the same in all 3 criteria, eg teacher had same post code, subject and age phase and student criteria, you get a teacher who only matches 2 criteria or none at all. So I got this far.
    So the text split, i have allow 10 fields for each post code, subject and age phase, in case more matches now or later. So each array is 10 fields wide and I need a way to search the 3 arrays to give me the names of the teachers who match the students in each role and put their names in an empty cell. How please.

    1. Hi Diane…To achieve this in Excel, you can use a combination of `INDEX`, `MATCH`, and `FILTER` functions to search through the arrays and find the teachers who match the given criteria for each student. Here’s a step-by-step guide:

      ### Step 1: Prepare Your Data
      Assume you have the following setup:

      – `A2:A11`: Teacher Post Codes
      – `B2:B11`: Teacher Subjects
      – `C2:C11`: Teacher Age Phases
      – `D2:D11`: Teacher Names
      – `F2`: Student Post Code
      – `G2`: Student Subject
      – `H2`: Student Age Phase
      – `I2`: Cell where you want to display the matched teacher names

      ### Step 2: Create Helper Columns
      You can use helper columns to create a unique identifier for matching:

      1. **Concatenate Teacher Criteria:**
      In cell `E2`, enter the formula to concatenate the criteria:
      “`
      =A2 & “-” & B2 & “-” & C2
      “`
      Drag this formula down to fill `E2:E11`.

      2. **Concatenate Student Criteria:**
      In cell `J2`, enter the formula to concatenate the student’s criteria:
      “`
      =F2 & “-” & G2 & “-” & H2
      “`

      ### Step 3: Use Array Formulas to Find Matches
      To find the teachers that match the student’s criteria, you can use an array formula combined with the `TEXTJOIN` function.

      1. **Find Matches:**
      In cell `I2`, enter the following formula:
      “`excel
      =TEXTJOIN(“, “, TRUE, IF(E2:E11 = J2, D2:D11, “”))
      “`
      Press `Ctrl+Shift+Enter` to enter it as an array formula if you are using an older version of Excel. In newer versions (Excel 365 or Excel 2019), you can just press `Enter`.

      ### Step 4: Display the Result
      The formula in `I2` will display the names of the teachers who match all three criteria for the student.

      ### Explanation
      – **Concatenation:** The helper columns concatenate the criteria into a single string for easier comparison.
      – **TEXTJOIN:** Combines the names of the teachers who match the criteria.
      – **IF Statement:** Checks if the concatenated criteria match and returns the teacher’s name if they do, otherwise returns an empty string.

      This approach assumes you have a limited number of teachers and students, making it feasible to use these array formulas. If you have a larger dataset or need a more dynamic solution, you might consider using Excel’s Power Query or VBA for more complex matching logic.

Leave a Reply

Your email address will not be published. Required fields are marked *