Google Sheets Formula For IF Contains – 2 Best Alternatives

Whether you want to get the total value in your dataset or you want to look for the lowest amount of sales in your company’s operations report an if contains formula comes in handy.

The if-condition statement is triggered if a particular cell “contains” a specific string or substring.

That said, the Google Sheets formula for IF Contains is non-existent.

However, there are two alternatives you can use.

Google Sheets Formula For IF Contains

To use the Google Sheets Formula for IF Contains you have two options:

• Use the REGEXMATCH function
• Use the SEARCH function

The 2 Methods in Google Sheets For IF Contains

Along with the REGEXMATCH and SEARCH functions, we use the IF function.

This will allow us to constitute the “contains” part of “IF Contains” with REGEXMATCH and SEARCH acting as the checked if a string does contain the string we are looking for.

Below is the definition of the IF function from Google.

The ‘IF’ function returns one value if a logical expression is TRUE and another if it is FALSE.

Often, other logical expressions or checks like the Not Equal To sign are used but in this case, we’re going to be using REGEXMATCH and SEARCH separately to check if a string contains a certain value.

The IF Syntax in Google Sheets

IF(logical_expression, value_if_true, value_if_false) logical_expression – An expression or reference to a cell containing an expression that represents some logical value, i.e. TRUE or FALSE.

value_if_true – The value the function returns if logical_expression is TRUE. value_if_false – [ OPTIONAL – blank by default ] – The value the function returns if logical_expression is FALSE.

The REGEXMATCH function in Google Sheets

The first Google Sheets formula for IF Contains is using the REGEXMATCH function along with the IF function.

Below is the definition of the REGEXMATCH function from Google.

REGEXMATCH(text, regular_expression) text – The text to be tested against the regular expression. regular_expression – The regular expression to test the text against

‘Regexmatch’ checks whether a piece of text matches a regular expression.

The REGEXMATCH Syntax

Using the REGEXMATCH function as a Google Sheets formula for IF Contains requires us to pair it with the IF function. In doing so, we get:

=IF(REGEXMATCH(text, regular_expression), value_if_true, value_if_false)

This formula indicates that if REGEXMATCH indicates that the “text” contains the “regular_expression”, it will output true, and the IF function will output “value_if_true”. Otherwise, the IF function with output “value_if_false”.

For example:

For the 2nd entry, “King Kong!”, since it contains “King”, the REGEXMATCH function returned “TRUE” and because of that, the IF function returned “Royalty” as the expression in the ‘value_if_true’ parameter.

As for everything else, since they do not contain “King”, the REGEXMATCH function returned “FALSE” and with that, the IF function returned “Commoner” as the expression in the ‘value_if_false’ parameter.

The SEARCH function in Google Sheets

The second Google Sheets formula for IF Contains is using the SEARCH function along with the IF function.

Below is the definition of the SEARCH function:

‘Search’ returns the position at which a string is first found within the text, ignoring the case.

The SEARCH Syntax in Google Sheets

SEARCH(search_for, text_to_search, [starting_at])

search_for – The string to look for within text_to_search.
text_to_search – The text to search for the first occurrence of search_for
starting_at – [ OPTIONAL – 1 by default ] – The character within text_to_search at which to start the search.

Using the SEARCH function as a Google Sheets formula for IF Contains requires us to pair it with the IF function. In doing so, we get:

=IF(SEARCH(search_for, text_to_search), value_if_true, value_if_false)

This formula indicates that if SEARCH indicates that the “text_to_search” parameter contains the “search_for” parameter, it will output true, and the IF function will output “value_if_true”. Otherwise, the IF function with output “value_if_false”.

For example:

For the 1st entry, “123 Adobo Avenue.”, since it contains “do”, the SEARCH function returned a value and because of that, the IF function returned “Do” as the expression in the ‘value_if_true’ parameter.

As for the next three entries, since they do not contain “do”, the SEARCH function returned an error message, and with that, the IF function returned “#VALUE”.

To fix this, we’re going to use the IFERROR function.

=IF(IFERROR(SEARCH(search_for, text_to_search),FALSE), value_if_true, value_if_false)

With this modified formula, we get the following result:

Now that we’ve added the IFERROR function, we’re now getting the desired results.

Frequently Asked Questions About Google Sheets Formula For IF Contains

Conclusion On Google Sheets Formula For IF Contains

Currently, there is no IF Contains function in Google Sheets. As a workaround use the IF function together with the REGEXMATCH and SEARCH function.