Skip to Content

How to use IF CONTAINS Google Sheets Formula (2 Quick Ways)

Posted on Last updated:

The IF function can be used to find a certain text string within the cell when combined with the SEARCH of REGEXMATCH functions in Google Sheets.

Manually identifying the cells containing a specific text string is time-consuming.

In the case of a single cell within a dataset containing a certain text string, you may choose to use the Find and Replace tool in Google Sheets to identify that specific cell.

But what if you wish to identify multiple cells containing multiple text strings?

Google Sheets has the solution for you!

In this article, let us learn to use the IF CONTAINS Google Sheets Formula to identify all the cells containing a specific text string. We will dive deep into the details and use cases of the IF, SEARCH, and REGEXMATCH functions.

We will also learn to test cell content against multiple text strings. So, make sure to read the article till the end.

Here’s what we will discuss in this article. Click on the links to jump to that particular section.

Before we move further and start discussing the actual steps, let’s have a quick look at the Google Sheets functions used in this article.

Explained: The IF Function in Google Sheets

The IF is a simple function in Google Sheets. It is used to return specific values if a certain condition is satisfied by the cells.

You can call it a conditional or comparison formula, which is based on a logical expression.

Here’s the general syntax for the IF function:

=IF(logical_expression, value_if_true, value_if_false)

Let’s quickly discuss the IF function arguments,

  • “logical_expression” – As from the name itself, this is the condition or criteria that needs to be satisfied by the cells. It can be either TRUE or FALSE. This argument triggers the next arguments of the function
  • “value_if_true” – Based on the results obtained by the “logical_expression” argument, what you wish to display if the value is TRUE. In simple words, the specific text string you wish to display if the cell satisfies a certain condition
  • “value_if_false” – Here, you can define the text string that you wish the IF function to return if the criteria are not satisfied. The working of this argument is similar to the previous one. It is an optional argument. If you don’t define it, then the function will simply return a blank value

Note that the “logical_expression” argument uses various operators to define criteria or conditions. However, in the case of this article, we won’t use any of those operators but instead leverage other functions in Google Sheets to define the criteria or conditions.

The IF is a powerful function in Google Sheets. It can be combined with other functions to create advanced formulas and obtain complicated results.

Explained: The REGEXMATCH Function in Google Sheets

It is a regular expression that allows users to find a substring in the cell.

The REGEXMATCH is a simple function that returns,

  • TRUE – If it finds the text string within the cell
  • FALSE – If the text string is missing

The general syntax for this function is as follows:

=REGEXMATCH(text,regular_expression)

Each argument of the REGEXMATCH function needs to be replaced with the proper information.

  • “text” – As the name suggests, it represents the main string in which you will be finding a specific text string. You can either provide the cell reference holding the main string or enter it manually
  • “regular_expression” – Here, you need to define the text string you wish to search within the cell or main string. You can manually enter the values or provide the cell reference holding the substring. Make sure to use double quotation marks while entering the values manually

Note that the “regular_expression” argument is powered by metacharacters. There are tens of metacharacters available in Google Sheets to use. But, we won’t discuss them here as it is a different topic.

You can create a really complex formula to find multiple text strings within the cell. It all comes down to your knowledge of regular expressions and creativity.

For the sake of this article, let’s just focus on replacing the “regular_expression” argument with the text string (substring) that you wish to search.

Explained: The SEARCH Function in Google Sheets

The SEARCH function in Google Sheets allows users to find a specific text string. The main goal of this function is to return the specific position of the text string within the cell.

Here, we will use it as a conditional formula that returns an integer value when certain criteria have been satisfied and a “#VALUE!” error when the criteria are not satisfied.

It is a simple formula with the following general syntax,

=SEARCH(search_for, text_to_search, [starting_at])

Let’s quickly discuss each argument of the SEARCH function,

  • “search_for” – This argument needs to be replaced with the text string you wish to find within cells. You can manually type the value or use the cell reference holding the substring. Note that if you choose to manually enter the value, then make sure to use the double quotation marks
  • “text_to_search” – It is the main string against which you will test the substring from the previous argument to return the results. Here, as well, you can choose to manually enter the values or provide the cell reference. Note that using the double quotation marks is compulsory if you choose to enter the values manually
  • “starting_at” – Use this argument to define the position from where the function should start the search. Simply put, if we put “2” as the input value here, then the function will start searching for a specific substring from the second character of the main string. It is an optional argument. If not defined, then takes default value as “1” and starts testing the substring against the main string from the first character

The SEARCH function is pretty straightforward to use.

In this article, we will use the first two arguments of the formula. Instead of using the actual integer number obtained using this formula, we will just treat it as the condition that has been met.

Copy the Example Google Sheet for IF CONTAINS Google Sheets Formula

Click on the following link to make a copy of the Google Sheet that contains the datasets we have used in the following sections.

Click Here to Copy!

Make sure to follow me as I take you through this article. It will surely strengthen your knowledge of the Google Sheets function used in this article.

It is okay if you have your own Google Sheet ready. You can skip downloading the above file.

How to check if a cell contains a specific text string using the IF + REGEXMATCH

Let us use the combination of the IF and REGEXMATCH functions in Google Sheets to find a text string within multiple cells of the column.

Task: Find the employee names working in the IT department.

Here are the steps,

  • Click on the cell “C2”
  • Type “=if”
  • Select the first option from the popup or press “Tab” on your keyboard
  • Now, in the place of “logical_expression” argument type “REGEXMATCH” on your keyboard

    (As REGEXMATCH is a function in Google Sheets, a popup will be displayed, as shown in the above image)
  • Select the first option or press the “Tab” key
  • We will be prompted to replace the “text” argument of the REGEXMATCH function with proper information. Let’s put the cell reference as “B2”
    (The B2 is the cell where we will be searching the keyword “IT” to return the results)
  • Press “,” to move to the next argument
  • Next, you need to type “IT” in the place of the “regular_expression” argument
    (Make sure to use double quotation marks; otherwise, the formula won’t work at the end)
  • Close the bracket using “)” for the REGEXMATCH function
  • Now, press “,” to move to the next argument of the IF function
  • Use the double quotation marks and type “Yes”. The IF function will display “Yes” as an output if the value generated by REGEXMATCH will be TRUE
  • Press “,” key
  • Type “No”. The IF function will provide the output as “No” if the REGEXMATCH function returns FALSE
  • Complete the parentheses using “)”
  • Press “Enter”

The final formula should look like this:

=IF(REGEXMATCH(B2,"IT"),"Yes","No")

To apply the above formula for the rest of the cells, use the autofill feature as shown in the above GIF or select the cell “B2” and hover to the lower-rightmost corner over the Fill Handle. You will notice a “+” icon. Click on it and drag it to the end of the table.

Q. Can we test the cell content against multiple text strings?

Yes. As mentioned earlier, the “regular_expression” argument is empowered by the metacharacters. They are very powerful and allow you to create complex formulas for advanced analysis.

Task – Find the employee names working in the IT and Sales department.

Let’s begin,

  • Click on the cell “B2”
  • Type “=if”
  • Select the first option or press “Tab” on your keyboard
  • Next, type “REGEXMATCH”
    (As REGEXMATCH is a function in Google Sheets, a popup will be displayed, as shown in the above image)
  • Press the “Tab” key to select the REGEXMATCH function
  • Provide the cell reference “B2”, which needs to be searched for the keywords “IT” and “Sales”
  • Press “,” to move to the next argument of the REGEXMATCH function
  • Now, type “IT|Sales” in double quotation marks
    (Note that here, we have used pipeline “|” as the metacharacter for the regular_expression argument)
  • Close the bracket using “)” for the REGEXMATCH function
  • Press “,” key
  • Type “Yes”, which needs to be displayed if the REGEXMATCH provides the TRUE as an input to the IF function
  • Press “,” key
  • Type “No”. It will be displayed as an output if the REGEXMATCH provides the FALSE as an input to the IF function
  • Complete the parentheses using “)” for the IF function
  • Press “Enter”

Here’s the final formula,

=IF(REGEXMATCH(B2,”IT|Sales”),”Yes”,”No”)

Note that the pipeline(“|”) symbol plays a crucial role here. The REGEXMATCH uses it as the metacharacter to do the magic.

You can search for any number of text strings using this metacharacter.

How to check if a cell contains a specific text string using the IF + SEARCH

This method follows the same steps as discussed above using the SEARCH function.

Task – Find the employee names working in the IT department.

So, let’s get started.

  • Select the cell “B2”
  • Type “=if”
  • Select the first option from the popup or press “Tab” on your keyboard
  • Now, replace the logical_expression argument of the IF function with “SEARCH”. As the SEARCH is also a function in Google Sheets, you will see a popup as shown below
  • Select the first option from the popup
  • Next, the SEARCH argument will ask you to replace the “search_for” argument with proper information
  • Type “IT”
  • Press “,” to move to the next argument within the SEARCH function
  • Provide the cell reference as “B2”
  • Complete the bracket using the “)” on your keyboard
  • Press “,” key
  • Now, you will taken back to the IF function
  • Replace the “value_if_true” argument with the “Yes”. It will display the keyword “Yes” if the SEARCH function would find the “IT” within the cell B2
  • The “value_if_false” argument is optional. So, we won’t go for it.
    (Note that another reason is that the IF function will return a “#VALUE!” error regardless of any value at the “value_if_false” argument. It is due to the SEARCH function, which returns a “#VALUE!” error when it is not able to find the text string within the cell)
  • Complete the brackets using “)”
  • Press “Enter”

Our final formula using the SEARCH function is as follows,

=IF(SEARCH("IT",B2),"Yes")

To apply this formula for the rest of the cells, hover to the lower-rightmost corner of the cell “C2” over the Fill Handle.

You will notice a “+” icon. Click on it and drag it to the end of the table.

Here’s the output:

The IF function has returned “#VALUE!” and the reason is “In SEARCH evaluation, cannot find ‘IT’ within ‘Sales’.” as shown in the following image.

Final Words

There is no IF CONTAINS function/formula in Google Sheets.

You need to combine the IF function with the REGEXMATCH or SEARCH function to find a text string within the cell.

I hope you learned all the bells and whistles of IF Contains Google Sheets formulas.

Let us know in the comment below if you are stuck somewhere or having any particular error while finding a text string using the methods discussed above.

Also, don’t forget to check our blog. We have plenty of articles that guide you about using Google Sheets for organizing and analyzing datasets.