The IF CONTAINS function in Google Sheets can be used to look for cells that contain a certain string as its value or as a portion of its value. It works as follows:
If range contains string, then value if true; else, value if false
While there is no explicit CONTAINS function in Google Sheets, it does not mean that you cannot pull this off! You can combine the IF function with the SEARCH and REGEXMATCH functions to get the same result. Learn how to do it by following this tutorial.
We will have two syntax forms of IF CONTAINS formula.
=IF(REGEXMATCH(range, string), value_if_true, value_if_false)
range is the reference of the cell in which we want to search
string is the string (can be numbers as well) we want to search for
value_if_true is the value to be returned if the string is found
value_if_false is the value to be returned if the string is not found
=IFERROR(IF(SEARCH(string, range), value_if_true), value_if_false)
where
string is the string (can be numbers as well) we want to search for
range is the reference of the cell in which we want to search
value_if_true is the value to be returned if the string is found, enclosed in double quotes
value_if_false is the value to be returned if the string is not found, enclosed in double quotes
We wrap the statement in IFERROR because the SEARCH function will output an error if the string is not detected in the selected range.
You can check our sample sheet here.
In this example we will use the IF function in combination with the REGEXMATCH function to compile a list of people who have achieved a “Matric” certificate in their final exams.
The string that we will look for is the “Matric” string in the column labeled Graduation. The function will be placed on a column labeled Message.
If contains google sheets range to scan
We will place our combined IF-REGEXMATCH function in this column headed “Message”, to return “Congratulations” for every person who has achieved a Matric certificate, or “VALUE!” for everyone else.
We set the following values with Row 2 as example:
String: “Matr”
Range: E2
Value_if_true: “Congratulations”
Value_if_false: “None”
The function will become
=if(regexmatch(E2,"Matr"),"Congratulations","None")
We shorten “Matric” to “Matr” to show that our function works to detect whether the range contains the given text or string.
Note that REGEXMATCH is case-sensitive, that’s why Row 4 is not detected as it lists “matric” instead of “Matric”.
For a case-insensitive solution check the next example.
We will do the previous example but we will use the SEARCH function this time.
The string that we will look for is the “Matric” string in the column labeled Graduation. The function will be placed on a column labeled Message.
We will place our combined IF-SEARCH function in this column headed “Message”, to return “Congratulations” for every person who has achieved a Matric certificate, or “VALUE!” for everyone else.
We set the following values with Row 2 as example:
String: “Matr”
Range: E2
Value_if_true: “Congratulations”
Value_if_false: “None”
The function will become
=iferror(if(search("Matr",E2),"Congratulations"),"None")
We shorten “Matric” to “Matr” to show that our function works to detect whether the range contains the given text or string.
The difference this time is that SEARCH is a case-insensitive function; so it detects “matric” as the same as “Matric”, marking it as well.
Even though there is no explicit IF CONTAINS function in Google Sheets, we can combine IF with the REGEXMATCH and SEARCH functions to get the same results. The IF+REGEXMATCH solution is best for case-sensitive searches while the IF+SEARCH solution is best for case-insensitive searches.
If you want to learn how to create a Google Sheets contact list template, we also suggest checking out our detailed guide.
Highlight a Row Based on a Cell Value in Google Sheets
Conditional Formatting with Multiple Conditions in Google Sheets
