How to Search IF Cell Contains in Google Sheets

Glad that you decided to check out TeqTog’s article on how to search IF cell contains in Google Sheets. In this article, I will try to introduce this cool feature to you available in Google Sheets, tell you different approaches to it, summarize a short list of their pros and cons and explain to you, how you can use this feature.

So, let’s get right to it, then?

The IF Cell Contains Feature

I think the name of this feature in Google Sheets is pretty self-explanatory. The IF cell contains is a tool which lets you search a cell to identify if it contains a certain value in Google Sheets.

This is very useful, right? Imagine a sea of data on your spreadsheets and you need to check a cell for a certain value- well, this is where Google Sheet’s IF cell contains will come in handy.

So, I am going to show three different approaches to using this tool and explain to you how you can apply all of them on your own.

How to Search IF Cell Contains in Google Sheets

Methods of using the IF Cell Contains Tool

Basically, there are three ways you can work with this tool and they would be:

  1. REGEXMATCH
  2. IF7SEARCH
  3. COUNTIF

I will start with the first method, okay?

The REGEXMATCH Method

1. You should have a set of data like the one shown in my example below:

The REGEXMATCH Method

As you can see, I have the first column which has a list of my data and the second column is for applying IF Cell Contains formula and getting its results.

The syntax of the REGEXMATCH method would be:

=regexmatch (text, regular_expression)
  1. The equal sign is given before any formula input
  2. Text refers to the cell I am searching.
  3. Regular expression means the certain data that I want to see in that cell.

2. Suppose that, I want to search the cell A:2 for the expression “Covid”. Now, to do that, I will give my formula.

syntax of the REGEXMATCH method
cells contains the expression at one glance

Now, I can view which of the cells contains the expression “Covid” at one glance.

Remember, that the REGEXMATCH formula is sensitive to exactly how you type your expression. If I had typed in “covid” instead of “Covid”, then the formula would have returned FALSE  for every cell; because then, it would be searching for “covid” with a lower case C and not an uppercase C.

Pros of REGEXMATCH

  1. It is very easy to learn and utilize.
  2. It gives you results in very short time.

Cons of REGEXMATCH

  1. It does not search numerical values.

Since, REGEXMATCH does not search and identify numbers; you can always change the cells with the numerical values to “Plain Text” from the “Format” option from the top menu bar.

The IF & SEARCH Method

This technique is actually a clever combination of the “IF” and “Search” function in Google Sheets. Google Sheets will search a cell to see if it contains that particular data and, if it does- Google Sheets will return me with positive text and if not, a negative text.

The syntax of this formula will be:

=IF (SEARCH(“Covid”, A:2) > 0, 1, 0)
  1. If is seeing whether the cell has that value.
  2. Search is searching if the cell has that value.
  3. “Covid” is the value I am searching for.
  4. A:2 is the cell I am searching in.
  5. 1 is what I would like Google Sheets to return, if the cell contains the value “Covid”.
  6. 0 is what I would like Google Sheets to return, if the cell doesn’t contain the value “Covid”.

1. I will take the dataset from my example:

IF SEARCH Method

2. In the cell no. B:2, I will give the input for my formula.

input of IF SEARCH Method formula
input of IF SEARCH Method formula part 2
final input of IF SEARCH Method formula

3. As you can see, in my example, I have given all of the inputs for each parameter of the entire formula.

the inputs for each parameter of the entire formula

So, Google Sheets has generated “1” for every cell which contains the expression “Covid” and generated the “Value” error symbol for every cell it could not identify “Covid” in.

If “#VALUE!” does not look very visually appealing to you, then you can apply filter to the column which has the results for your IF & SEARCH formula, filter out all the value errors and simply change all of them into something else- for instance, “no”.

  • It can identify numerical expressions.
  • It is not sensitive to upper case and lower case values.
  • It returns the “#VALUE!” symbol for every negative search result.

The COUNTIF Method

Coming to the last, but not the least method, is the “COUNTIF” method. The syntax for the CountIF formula will be:

=COUNTIF (range, criterion)
  1. Range would mean the cell I am searching in.
  2. Criterion is the data I am searching for.

Note: Make sure to caption your criterion into the star mark symbols (*)

1. As usual, I put in my formula.

COUNTIF Method
formula input for COUNTIF Method
results of COUNTIF Method

2. For each positive search result my spreadsheet gave me “1” and for every negative result it gave me “0”.

Pros of COUNTIF

  • The COUNTIF formula is not case sensitive.

Cons on COUNTIF

  • This function cannot search numbers like the REGEXMATCH. You will need to convert your numerical values into “Plain text”.  

So there you are! Three ways in how you can search if your cell contains a certain value on Google Sheets.

Best of luck!

You May Love To Read Our More Google Sheets Tutorial Articles on:

Was this article helpful?
YesNo


Related More Articles

Leave a Comment