In this tutorial, you will learn how to create a formula that acts as an IF CONTAINS function in Google Sheets.
How to Use IF CONTAINS in Google Sheets
The IF function is one of the most powerful functions available in Google Sheets. This is because it allows you to add custom logic to your spreadsheet that depends on the value of other cells.
One particular use case for the IF function is when you need to detect if a certain cell contains a particular string. While Google Sheets does not have its own IF CONTAINS function, there are a few workarounds.
We can create our own IF CONTAINS formula with either the REGEXMATCH or SEARCH functions. These functions are similar since they both allow you to check if a cell contains a particular string of text.
In this guide, we will show you how to use IF, REGEXMATCH, and SEARCH functions in Google Sheets.
How to Create a Conditional Formula that Checks for a Substring in Google Sheets
Here’s how to create a conditional formula that checks for a substring in Google Sheets.
Step 1
First, select an empty cell where we can place our IF CONTAINS formula.
In the example above, we have a list of strings that we want to analyze. We want to know if the string contains the name specified in cell B1.
Step 2
We can use the SEARCH function to determine if a particular cell contains a certain string. However, the SEARCH function does not simply return a TRUE or FALSE. The SEARCH function actually returns a number equivalent to the character placement of the found string. If no string is found, the SEARCH function will just return a #VALUE! error.
We’ll use the ISNUMBER function to determine whether the output of SEARCH indicates a matching string.
Step 3
We can also use the REGEXMATCH function to determine if a cell contains a particular string.
The REGEXMATCH function returns either TRUE or FALSE depending on whether the first argument follows the pattern indicated in the second parameter.
Step 4
Sometimes, you may want to know if a cell is equal to a particular string.
In these cases, we can instead use the equal operator.
Do note however, that this method is not case-sensitive.
Step 5
If you want to perform a case-sensitive check if two strings match, we can use the EXACT function instead.
The EXACT function only returns TRUE if both arguments have the same string and capitalization.
Summary
This guide should be everything you need to create an IF CONTAINS formula in Google Sheets.
You may make a copy of this example spreadsheet to test it out on your own.