IF CONTAINS Google Sheets Formulas [Complete Guide]

Today we will learn how to use IF CONTAINS Google Sheets Formula. The IF function of Google Sheets is the most powerful function of Google Sheets because with this function, we can create our own IF CONTAINS formula with either the REGEXMATCH or SEARCH functions. In this guide as well, today we are going to take an overview of IF contains Google Sheets formulas.

Why do we use IF CONTAINS Google Sheets Formulas?

If I say that 90% of operations in Google Sheets are operated by IF contains Google Sheets formulas then it would not be wrong, because it provides logical customization with the expressions. You can find if a certain cell contains a particular string by using IF contains Google Sheets formulas. Let’s move to the next section of the guide on IF contains Google Sheets formulas.

Syntax

=IF(logical_expression, if_true, if_false)

• Logical expression: the value of the cell address as true or false with condition or criteria.
• If_true: the value to be displayed if the expression is true.
• If_false: It is optional but can be used to define the value when the expression is false.

How to use IF CONTAINS Google Sheets Formulas

You can use IF contains Google Sheets formulas in various conditions depending on your data in the sheet. IF functions are used to conditionally test for expression. It depends on your criteria for what logical expression you are looking for. In this tutorial, we will see some of the basic uses of IF contains formulas in Google Sheets to understand the usage of IF contains Google Sheets formulas.

IF Function with Google Sheets operators

Step 1

In the following sheet, if we see we have sample data of sales of different months made by different stores. Now we want to find that we have gotten either profit or loss. Let’s see how we can find it with the help of the IF function.

Step 2

First place your cursor where you want to find your result and start the “IF” function. Once you have run the function, first we will give the cell reference of the value on which we are applying the condition.

Step 3

After giving the cell reference of the data value, specify the condition that you want to apply to the cell. I am looking for values that are greater than 200 so, we will write in the following pattern.

Step 4

Once you have given the criteria in the IF syntax, we will have to write the value that we find in return if the value is true where we are writing “Profit” as written below.

Step 5

Similarly, in the next argument, we will give the value that we will get in return if the value is false for which here, we are writing “Loss” as can be seen below.

Step 6

Once you have completed the syntax, just press the Enter key to get the result and drag the formula over the other cells to get all the result as you can see in the following picture the value that is greater than $200 are displayed by Profit and rest are displaying as Loss.

IF function with REGEXMATCH function

Step 1

In the following sample data, we will find the criteria of eligibility for the bonus if any store exists. Let’s see how we can find this with the help of the IF containing Google Sheets formulas.

Step 2

First, we will run the IF function by just writing IF with an equal sign then will add another function of “REGEXMATCH” to look for an exact match value in the data.

Step 3

After starting the REGEXMATCH function, we will have to give the cell reference of data value as given in the following example.

Step 4

After giving the data value, we will have to specify the expression for what we are looking for in quotation marks.

Step 5

Now, you can close the REGEXMATCH function, and to complete the IF function, we will have to specify the value if_true and If_false as I have written in the following syntax.

FORMULA: =IF(REGEXMATCH(K4,”Profit”),”Yes”, “No”)

Step 6

Pressing the Enter key will give you the following result the cell that contains the “Profit” will give you “Yes” in the resultant and the cell that doesn’t exist will display the “No” signal in the cell as given.

IF function with SEARCH function

Step 1

In the following sample data, we have a huge list of mail Addresses. In this list, I have to find the addresses of New York. Let’s see, how can we find it with the help of the “IF” function.

Step 2

Here, we will use a combination of different functions or different operations, we will run the “IF” function to apply the condition to give the value true or false, then the ISNUMBER function to look for the value, and then the SEARCH function to search the text from the cell.

Step 3

According to the SEARCH function, first, we will specify the value that we are searching for, as we are looking for New York so here we have written “New York” in the syntax.

Note: Must write the same text and value that you are looking for, if there is any capital case in the word write capital case, if there is a space in the text must use space while specifying in the syntax.

Step 4

After specifying the value, we will have to give the cell address in which we are looking for the value as I have given in the following picture.

Step 5

After completing the SEARCH function, complete the IF function by giving the value that we will get in return if the value is true or false where we have written “Found” and “Not” for true and false respectively.

FORMULA: =IF(ISNUMBER(SEARCH(“New York”,B2)),”Found”,”Not”)

Step 6

You are done now, just press the Enter key and drag the formula over the other cells to get the result. As we can see in the following picture, if there is an address regarding New York it is indicated by “Found” and the rest are by “Not“.

Frequently Asked Questions

How to apply multiple conditions using IF contains Google Sheet formulas?

If your data set has multiple expressions and you want to apply multiple criteria in your IF contains a formula, then you can also build a combination of functions with multiple conditions in one syntax, If you are wondering if is it possible to apply multiple conditions in a single syntax then follow the following instructions to see how can we apply multiple conditions using IF contains Google Sheets formulas.

Step 1

As you see in the following picture, I have applied an IF function to specify that If the cell is equal to “A” then give it the remark “Excellent” as you can see in the syntax below.

Step 2

In the same syntax, you can also add another IF function to specify another condition. For example, I have specified if the cell is equal to “A+” and then given it the remark “Fabulous” as highlighted in the following picture.

Step 3

Similarly, just add a comma sign and add another IF function to specify another condition as I have specified below.

Step 4

You can add different conditions in your syntax according to your data with the help of using a combination of IF functions in Google Sheets.

FORMULA: =IF(D26=”A”,”Excelent”,IF(D26=”A+”,”Febulous”,IF(D26=”B+”,”Good”,if(D26=”B”,”Fair”,IF(D26=”F”,”Poor”)))))

Step 5

The result for the above syntax will be as follows when you press the Enter key as per the given rules.

Conclusion

Hope with the help of the above article on IF contains Google Sheets formula, you won’t have any trouble in using IF contains Google Sheets formulas, if you are facing any problem with any IF contains Google Sheets formula, must tell us in the comment section, Office Demy is always there to make a solution.