REGEXMATCH multiple criteria in Google Sheets

If you work in the area of data visualization in Looker Studio, for sure you have to work in Google Sheets to prepare your data. Today I would like to share a case with the REGEXMATCH Google Sheets formula. 

Task

I have a sheet with absolutely random CRM data, like dates, revenues, UTM sources, campaign names etc. Our task is to fill two columns – language and type – with data from campaign names. To clarify, let’s move to the Rules tab. Here we can see the constituents of the Campaign names. 

There are three countries according to which we will write a language. If the country is the UK or the US, the language is English, if it is France, the language is, consequently, French. That is the rule for the Language column. 

There also are three months – August, September and October. Beside that, there are four ad types – Search, YouTube, Brand and Discovery. So, our task is to use the REGEXMATCH formula to fill the Type column. 

Setting up the rule

The mechanism of the formula works like this: if the trigger is found in a cell, some word will be displayed in another cell. Basically, all the type names are triggers. So, if the word in the campaign name is Search or Brand, the type will be PPC, and if the campaign name contains YouTube or Discovery, the type will be Video. Followingly, we need to write type options that in our case are only two – PPC and Video.

Next step is preparing the rules for REGEXMATCH. We write a formula by selecting the cells. Verbally the formulas can be explained like this: 1) if the trigger is Search or “divider” Brand, it will be PPC. 2) if the trigger is YouTube or “divider” Discovery, it will be Video.

Test 

In a Corner that I’ve created to test the formula I have some campaign names and columns for languages and types. Languages column is already filled in. Let’s use the REGEXMATCH formula to fill the type column. The formula looks like this: =REGEXMATCH (A11,$H$12). It can be explained like “check if the campaign name contains a trigger written in a certain cell”. After that we will see that the fields are filled with “True” or “False”. So, if the campaign name contains Search or Brand, the result is “True”, if not, “False”. 

Let’s expand this formula to work the way we want. It can be worded like “If this REGEXMATCH expression is true, so use a certain cell, and if this REGEXMATCH expression is false, so use data from another cell”. Applying this formula to all the columns, we can see that now it looks good. 

But, there is a small moment. If there is no campaign name or the formula didn’t find anything, it shows an error. As this data will supposedly be used in Looker Studio, there shouldn’t be any errors. So, let’s add to our formula “if error…, “Other”. This will help us avoid errors.

As we don’t know the exact number of the rows, let’s also add an array formula. In this case, I should change the exact cell where the trigger is going to be searched to a range.

Here is what the final formula looks like:

=ARRAYFORMULA( IFERROR( ifs(REGEXMATCH(C2:C,Rules!$H$12),Rules!$G$12,regexmatch(C2:C,Rules!$H$13),Rules!$G$13)))  

Hope this article was informative and interesting for you! Leave your impressions in the comments!

You can read more articles of mine in my blog!