In this article:

IF CONTAINS Google Sheets (The Easy Way!)

December 5, 2024

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. 

IF CONTAINS Google Sheets Syntax

We will have two syntax forms of IF CONTAINS formula. 

IF+REGEXMATCH Syntax

=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

IF+SEARCH Syntax

=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. 

IF CONTAINS Google Sheets Examples

You can check our sample sheet here.

Check If Cell Contains Specific Text in Google Sheets Using IF + REGEXMATCH

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.

Step 1: Identify the string and where to search

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.

Step 2: Add the function =IF(REGEXMATCH(range, string), value_if_true, value_if_false)

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. 

Step 3: Check output

If contains google sheets using if plus regexmatch

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. 

Check If Cell Contains Specific Text in Google Sheets Using IF+SEARCH

We will do the previous example but we will use the SEARCH function this time.

Step 1: Identify the string and where to search

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-SEARCH function in this column headed “Message”, to return “Congratulations” for every person who has achieved a Matric certificate, or “VALUE!” for everyone else.

Step 2: Add the function =IFERROR(IF(SEARCH(string, range), value_if_true), value_if_false)

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. 

Step 3: Check output

If contains google sheets using if plus search

 

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. 

Conclusion

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. 

Supercharge Your Spreadsheets with Lido

🚀 Import data from anywhere and build custom tools and powerful dashboards straight from your spreadsheet. Discover what's possible in our Build Gallery.

Related IF Functions 

IF: Allows you to check for specific conditions across a dataset

SUMIF: Use to sum numbers if they meet a certain condition..

SUMIFS: Sums data from cells that meet multiple criteria

COUNTIF: count data if it fulfils certain criteria

COUNTIFS: Count data that fulfils two or more criteria.

COUNTIF Not Null: Count cells if they contain data

IFS: IFS is a more elegant way to evaluate data against multiple criteria. 

IFERROR: Allows you to output values when an Error in a formula occurs

IF THEN: Allows you tor write statements that use IF X Then Y Logic

IF AND: Combines the functionality of the IF and AND functions



IF Else: Set conditions that give an output depending on whether a given condition is fulfilled or not


Multiple IF Statements: How to chain multiple statements together.

IF OR: Combines the functionality of the IF and OR functions

AVERAGE IF: Calculate an average for numbers within a data range if they meet the provided criteria. 

If you want to learn how to create a Google Sheets contact list template, we also suggest checking out our detailed guide. 

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->