Today we are going to learn IF CONTAINS Google Sheets Formulas Because it allows us to design our IF CONTAINS formula using either the REGEXMATCH or SEARCH capabilities, Google Sheets’ IF function is the most powerful. We’ll go over an overview of IF includes Google Sheets formulae in this article as well.
Why do we need to use IF CONTAINS Google Sheets Formulas?
Classification of data: Sort a cell according to a particular category if it has a particular term in it.
Example: To indicate that a cell contains the word “Expense“, designate.
Formatting under conditions: Apply formatting (such as color changes) according to whether a particular text is present in a given cell.
Example: Set the background color of a cell to red if it has the word “High” in it.
Sorting or filtering: Sort or filter data according to whether or not a particular text appears in a cell in a given column.
Example: arrange a list of chores according to whether they use the word “Urgent”.
Syntax: =IF(logical_expression, if_true, if_false)
- Logical Expression: The cell address’s value is expressed logically as true or false depending on a set of conditions or criteria.
- If true: If the expression is true, the value to be shown is If_true.
- If false: This expression can be used to define the value in the event that it is false, but it is optional.
You may also like>>> How To Create Barcodes In Google Sheets [2 Ways]
How to use IF CONTAINS Google Sheets Formulas
IF includes Google Sheets formulae that you can employ under different circumstances based on the data you provide in the sheet. To conditionally test for expression, one can utilize IF functions. What logical expression you are looking for will depend on your requirements. To help you learn how to utilize IF including formulas in Google Sheets, we’ll go over some of the fundamental applications for them in this tutorial.
IF Function with Google Sheets operators
Step 1>
We can view example statistics of sales for various months from various stores on the following sheet. We now seek to ascertain if we have realized a profit or a loss. Let’s see if we can use the IF function to help us find it.
![IF CONTAINS Google Sheets Formulas IF CONTAINS Google Sheets Formulas](https://officechaser.com/wp-content/uploads/2023/12/IF-CONTAINS-Google-Sheets-Formulas-1.png)
Step 2>
Start the “IF” function by first placing your cursor where you want to find your result. After the function has been executed, we will first provide the cell reference of the value that the condition is being applied to.
![IF CONTAINS Google Sheets Formulas IF CONTAINS Google Sheets Formulas](https://officechaser.com/wp-content/uploads/2023/12/IF-CONTAINS-Google-Sheets-Formulas-2.png)
Step 3>
Once the data value’s cell reference has been provided, indicate the condition you wish to apply to the cell. Since I’m searching for numbers higher than 200, we’ll write in the format shown below.
![IF CONTAINS Google Sheets Formulas IF CONTAINS Google Sheets Formulas](https://officechaser.com/wp-content/uploads/2023/12/IF-CONTAINS-Google-Sheets-Formulas-3.png)
Step 4>
After providing the criterion in the IF syntax, we must write the value returned if the value is true, in which case we should write “Profit“, as shown below.
![IF CONTAINS Google Sheets Formulas IF CONTAINS Google Sheets Formulas](https://officechaser.com/wp-content/uploads/2023/12/IF-CONTAINS-Google-Sheets-Formulas-4.png)
Step 5>
Similar to this, in the following argument, we will state the value we will receive in return in the event that the value is false; in this case, we are writing “Loss“, as shown below.
![IF CONTAINS Google Sheets Formulas IF CONTAINS Google Sheets Formulas](https://officechaser.com/wp-content/uploads/2023/12/IF-CONTAINS-Google-Sheets-Formulas-5.png)
Step 6>
After completing the syntax, all you need to do is hit Enter to see the result, then drag the formula over the remaining cells to view the entire result. As you can see in the following image, values more than $200 are shown as profit, and the remaining values are shown as loss.
![IF CONTAINS Google Sheets Formulas IF CONTAINS Google Sheets Formulas](https://officechaser.com/wp-content/uploads/2023/12/IF-CONTAINS-Google-Sheets-Formulas-6.png)
IF function with REGEXMATCH function
Step 1>
If there are any stores, the requirements for qualifying for the bonus can be found in the example data that follows. Let’s see if we can use the IF that contains Google Sheets formulas to discover this.
![IF CONTAINS Google Sheets Formulas IF CONTAINS Google Sheets Formulas](https://officechaser.com/wp-content/uploads/2023/12/IF-CONTAINS-Google-Sheets-Formulas-11.png)
Step 2>
Initially, we will just write the IF function with an equal sign. Next, we will add the “REGEXMATCH” function to search the data for an exact match value.
![IF CONTAINS Google Sheets Formulas IF CONTAINS Google Sheets Formulas](https://officechaser.com/wp-content/uploads/2023/12/IF-CONTAINS-Google-Sheets-Formulas-12.png)
Step 3>
The cell reference for the data value must be provided once the REGEXMATCH function has been started, as shown in the example below.
![IF CONTAINS Google Sheets Formulas IF CONTAINS Google Sheets Formulas](https://officechaser.com/wp-content/uploads/2023/12/IF-CONTAINS-Google-Sheets-Formulas-13.png)
Step 4>
We must include the term for what we are looking for in quotation marks after assigning the data value.
![IF CONTAINS Google Sheets Formulas IF CONTAINS Google Sheets Formulas](https://officechaser.com/wp-content/uploads/2023/12/IF-CONTAINS-Google-Sheets-Formulas-14.png)
Step 5>
You can now end the REGEXMATCH function. In order to finish the IF function, we need to provide the values if_true and If_false, as seen in the syntax I’ve provided.
![IF CONTAINS Google Sheets Formulas IF CONTAINS Google Sheets Formulas](https://officechaser.com/wp-content/uploads/2023/12/IF-CONTAINS-Google-Sheets-Formulas-15.png)
FORMULA: =IF(REGEXMATCH(E3,”Profit”),”Yes”, “No”)
Step 6>
When you hit the Enter key, the following result appears: the cell that has “Profit” in it will result in “Yes“, and the cell that doesn’t exist will show the supplied “No” signal in the cell.
![IF CONTAINS Google Sheets Formulas IF CONTAINS Google Sheets Formulas](https://officechaser.com/wp-content/uploads/2023/12/IF-CONTAINS-Google-Sheets-Formulas-16.png)
IF function with SEARCH function
Step 1>
An extensive list of mail addresses can be seen in the sample data below. I need to locate the addresses for New York in this list. Let’s see how we can use the “IF” function to find it.
![IF CONTAINS Google Sheets Formulas IF CONTAINS Google Sheets Formulas](https://officechaser.com/wp-content/uploads/2023/12/IF-CONTAINS-Google-Sheets-Formulas-21.png)
Step 2>
Here, we’ll combine a few distinct functions or operations: first, we’ll use the “IF” function to apply the condition and return a true or false value; next, we’ll use the ISNUMBER function to find the value; and last, we’ll use the SEARCH function to seek for text within the cell.
![IF CONTAINS Google Sheets Formulas IF CONTAINS Google Sheets Formulas](https://officechaser.com/wp-content/uploads/2023/12/IF-CONTAINS-Google-Sheets-Formulas-22.png)
Step 3>
The SEARCH function states that we must first define the value we are seeking for. Since we are looking for New York, we have typed “New York” in this case.
![IF CONTAINS Google Sheets Formulas IF CONTAINS Google Sheets Formulas](https://officechaser.com/wp-content/uploads/2023/12/IF-CONTAINS-Google-Sheets-Formulas-23.png)
Note: You must enter the text and value that you are looking for. If a term has a capital letter, it must be written in capital letters. You must also utilize spaces in the text when indicating in the syntax.
Step 4>
Once the value has been specified, we must provide the cell address where the value is located, as shown in the following image.
![IF CONTAINS Google Sheets Formulas IF CONTAINS Google Sheets Formulas](https://officechaser.com/wp-content/uploads/2023/12/IF-CONTAINS-Google-Sheets-Formulas-24.png)
Step 5>
Once the SEARCH function has been finished, finish the IF function by providing the value that will be returned depending on whether the value is true or false. For true and false, we have typed “Found” and “Not“, respectively.
![IF CONTAINS Google Sheets Formulas IF CONTAINS Google Sheets Formulas](https://officechaser.com/wp-content/uploads/2023/12/IF-CONTAINS-Google-Sheets-Formulas-25.png)
FORMULA: =IF(ISNUMBER(SEARCH(“New York”,B18)),”Found”,”Not”)
Step 6>
Now that you’re done, simply hit Enter to see the result, then drag the formula over the remaining cells. As we can see in the image below, the words “Found” denote any addresses related to New York, while “Not” denotes the remaining addresses.
![IF CONTAINS Google Sheets Formulas IF CONTAINS Google Sheets Formulas](https://officechaser.com/wp-content/uploads/2023/12/IF-CONTAINS-Google-Sheets-Formulas-26.png)
You may also like>>> How To Create QR Codes In Google Sheets [2 Methods]
Frequently Asked Questions
How can I use Google Sheet formulas in an IF statement to apply several conditions?
You can also create a combination of functions with multiple conditions in one syntax if your data set contains multiple expressions and you wish to apply multiple criteria in your IF containing a formula. If you’re wondering whether it’s possible to apply multiple conditions in a single syntax, then follow the steps below to find out how to do so using IF contains Google Sheets formulas.
Step 1>
As you can see in the image below, I’ve used the IF function to say that, if the cell equals “A“, it should be marked as “Excellent“, as the syntax shows.
![IF CONTAINS Google Sheets Formulas IF CONTAINS Google Sheets Formulas](https://officechaser.com/wp-content/uploads/2023/12/IF-CONTAINS-Google-Sheets-Formulas-31.png)
Step 2>
To define a different condition, you can also add another IF function using the same syntax. For instance, as seen in the following image, I have indicated whether the cell equals “A+” and have then annotated it with the comment “Great“.
![IF CONTAINS Google Sheets Formulas IF CONTAINS Google Sheets Formulas](https://officechaser.com/wp-content/uploads/2023/12/IF-CONTAINS-Google-Sheets-Formulas-32.png)
Step 3>
Similarly, to express another condition, simply add another IF function and a comma, as I have indicated below.
![IF CONTAINS Google Sheets Formulas IF CONTAINS Google Sheets Formulas](https://officechaser.com/wp-content/uploads/2023/12/IF-CONTAINS-Google-Sheets-Formulas-33.png)
Step 4>
Using a variety of IF functions in Google Sheets, you can add various conditions to your syntax based on your data.
![IF CONTAINS Google Sheets Formulas IF CONTAINS Google Sheets Formulas](https://officechaser.com/wp-content/uploads/2023/12/IF-CONTAINS-Google-Sheets-Formulas-34.png)
Complete Formula :=IF(D38=”A”, “Excellent”, IF(D38=”A+”, “Great”, IF(D38=”B”, “Good”, IF(D38=”C”, “Fair”, IF(D38=”FAIL”, “Poor”, “”)))))
Step 5>
When you press the Enter key in accordance with the guidelines, the syntax mentioned above will appear as follows.
![IF CONTAINS Google Sheets Formulas IF CONTAINS Google Sheets Formulas](https://officechaser.com/wp-content/uploads/2023/12/IF-CONTAINS-Google-Sheets-Formulas-35.png)
You may also like>>> How To Calculate Hours Worked In Google Sheets [2 Ways]
Conclusion
Finally, adding “IF CONTAINS” logic to Google Sheets enables users to do conditional actions in response to particular text presence. Combining IF, SEARCH and REGEXMATCH allows for conditional formatting and dynamic data classification, but it is not a stand-alone function. Accept this flexibility to handle spreadsheets more effectively and flexibly, improving data analysis and reporting in Google Sheets.