This post explains how to exact match single as well as multiple strings using regular expression in Google Sheets.

Actually, I have already explained the same in some of my earlier tutorials. But the central topic of those tutorials was different.

Further, the exact match of multiple strings using regular expression (pattern match) can be part of my a few of the future tutorials.

So instead of writing the steps, again and again, I thought better to write a tutorial on regular expression for an exact match so that I can link it in relevant tutorials in the future. This tutorial is the outcome of that thought process.

There are two functions in Google Sheets for regular expression match. They are REGEXMATCH and QUERY.

The REGEXREPLACE and REGEXEXTRACT are the two other functions that use regular expressions. We do not require these two functions for exact math.

Two Functions that Supports Exact Match Using Regular Expression in Google Sheets

Actually, we can use the REGEXMATCH for an exact match and return TRUE or FALSE whereas the QUERY for exact match and filter a range.

Please do note that, similar to QUERY, using the FILTER function with REGEXMATCH, we can filter the exact match strings. But the aggregation capability of QUERY will still be missing.

Below is the sample array to test our formulas (exact match regular expression matches).

Sample Data Range: A1:B8

Regular Expression Exact Match in REGEXMATCH in Google Sheets

Single Criterion (Condition)

Assume I want to test whether cell A2 contains “Product 1”. Then I should use the REGEXMATCH formula as below.

=REGEXMATCH(A2,"^Product 1$")

This will return TRUE if cell A2 contains the string “Product 1”. This is an exact match using regular expression in Google Sheets. It will not match “Product” or “Product 11”.

In order to exactly match a single criterion in a whole column, use the ARRAYFORMULA function with the REGEXMATCH.

=ArrayFormula(REGEXMATCH(A2:A,"^Product 1$"))

Wait! Before using this formula you need to include a logical expression to limit the expanding of results to non-blank cells.

I am normally using an IF and LEN combination which tests whether the length of a cell is greater than or equal to 0. If the cell has content, the LEN will return the length of the characters else 0.

=ArrayFormula(if(len(A2:A)=0,,REGEXMATCH(A2:A,"^Product 1$")))

If you are not familiar with the IF and LEN combination, then here is one alternative formula.

=ArrayFormula(if(A2:A="",,REGEXMATCH(A2:A,"^Product 1$")))

What about Using the Criterion for an Exact Match as a Cell Reference?

Let’s insert the criterion is cell D1. Then in the formulas above, use the criterion as below.

=ArrayFormula(if(len(A2:A)=0,,REGEXMATCH(A2:A,"^"&D1&"$")))

Multiple Criterion (Conditions)

In order to exact match multiple conditions using a regular expression in Google Sheets, separate each criterion with a | symbol. The Caret and Dollar signs must be repeated for each criterion.

=ArrayFormula(if(len(A2:A)=0,,REGEXMATCH(A2:A,"^Product 1$|^Product 22$|^Product 2$")))

Criteria in D1:D3 (Fixed Range):

To refer to multiple criteria, we can write the criteria as below but not advised!

=ArrayFormula(if(len(A2:A)=0,,REGEXMATCH(A2:A,"^"&D1&"$|^"&D2&"$|^"&D3&"$")))

It will work but it is not flexible because the cell references are referenced individually. So, in the future, we may require to modify the formula to add more conditions. We can make it flexible as below.

Criteria in D1:D (Open Range):

="^"&textjoin("$|^",true,D1:D)&"$"

This will help you include more criteria in column D in the future.

So the formula will be;

=ArrayFormula(if(len(A2:A)=0,,REGEXMATCH(A2:A,"^"&textjoin("$|^",true,D1:D)&"$")))

Filter Exact Match Using FILTER and REGEXMATCH in Google Sheets

If you could understand the regular expression use for an exact match in Google Sheets, then the filtering part will be easy for you.

Syntax: FILTER(range, condition1)

You just want to use the REGEXMATCH formula as the FILTER ‘condition1’.

Example:

=Filter(A2:B,ArrayFormula(if(len(A2:A)=0,,REGEXMATCH(A2:A,"^"&textjoin("$|^",true,D1:D)&"$"))))

You can remove the Arrayformula( and one of the closing brackets from the end part as the FILTER formula in Google Sheets doesn’t require the ARRAYFORMULA function within.

Regular Expression Match in Query

If your purpose of the exact match using regular expression in Google Sheets is just to filter or filter and aggregate data, then QUERY is the better option.

In Query, we can use the MATCHES clause for regular expression match of single as well as multiple criteria.

You May Like: What is the Correct Clause Order in Google Sheets Query?

Single Criterion:

=query(A2:B,"Select A,B where A matches 'Product 1'")

When the criterion is in the cell D1 (please refer to Examples to the Use of Literals in Query in Google Sheets):

=query(A2:B,"Select A,B where A matches '"&D1&"'")

Here also for multiple criteria, we can use the | separator. But unlike REGEXMATCH, the ^ and $ sign around the criterion is not required.

Criteria Hardcoded into the Formula:

=query(A1:B,"Select A,B where A matches 'Product 1|Product 22|Product 2'")

Cel References:

=query(A1:B,"Select A,B where A matches '"&TEXTJOIN("|",true,D1:D)&"'")

For more details about QUERY regular expression match, please check this tutorial – Matches Regular Expression Match in Google Sheets Query.

If you don’t want an exact match, then you may opt for;

• CONTAINS substring match in Query for a maximum of 2-3 criteria.
• MATCHES for several criteria which I have explained in detail in my post Multiple CONTAINS in WHERE Clause in Google Sheets Query.

That’s all about the exact match using regular expression in Google Sheets. Enjoy!