Are you tired of trying to find the exact match for single or multiple strings in Google Sheets? Look no further! In this article, we will unravel the secrets of using regular expressions to achieve that elusive exact match. Trust me, it’s simpler than you think!
Two Functions that Support Exact Match Using Regular Expression in Google Sheets
Google Sheets provides two powerful functions, REGEXMATCH and QUERY, that are perfect for exact match using regular expressions. Let’s explore each of them and see how they can make your life easier.
REGEXMATCH: Single Criterion (Condition)
If you want to test whether a specific cell contains an exact match for a string, REGEXMATCH is your go-to function. It will return either TRUE or FALSE based on whether the exact match is found.
Here’s an example: let’s say you want to check if cell A2 contains the string “Product 1”. You can use the REGEXMATCH formula like this:
=REGEXMATCH(A2,"^Product 1$")This formula will return TRUE if cell A2 contains the exact string “Product 1”. It won’t match “Product” or “Product 11”.
To perform an exact match for a single criterion in a whole column, you can use the ARRAYFORMULA function along with REGEXMATCH. However, please remember to include a logical expression to limit the expansion of results to non-blank cells.
Here’s an example of how it can be done:
=ArrayFormula(if(len(A2:A)=0,,REGEXMATCH(A2:A,"^Product 1$")))If you are not familiar with the IF and LEN combination, don’t worry! There’s an alternative formula you can use:
=ArrayFormula(if(A2:A="",,REGEXMATCH(A2:A,"^Product 1$")))But what if you want to use the criterion for an exact match as a cell reference? No problem! Just insert the criterion in cell D1 and modify the formula accordingly:
=ArrayFormula(if(len(A2:A)=0,,REGEXMATCH(A2:A,"^"&D1&"$")))REGEXMATCH: Multiple Criterion (Conditions)
To achieve an exact match for multiple conditions using a regular expression in Google Sheets, simply separate each criterion with a “|” symbol. Repeat the Caret (^) and Dollar ($) signs for each criterion.
Here’s an example formula that will do the trick:
=ArrayFormula(if(len(A2:A)=0,,REGEXMATCH(A2:A,"^Product 1$|^Product 22$|^Product 2$")))You can even refer to multiple criteria by writing them in a fixed range (e.g., D1:D3). However, it is not advised because it lacks flexibility. If you need to add more conditions in the future, you’ll have to modify the formula. Instead, consider using an open range like this:
="^"&textjoin("$|^",true,D1:D)&"$"This formula allows you to include more criteria in column D whenever needed. So your final formula will look like this:
=ArrayFormula(if(len(A2:A)=0,,REGEXMATCH(A2:A,"^"&textjoin("$|^",true,D1:D)&"$")))Filter Exact Match Using FILTER and REGEXMATCH in Google Sheets
Now that you understand how to achieve an exact match using regular expressions in Google Sheets, let’s move on to the filtering part. It’s actually quite simple!
Here’s the syntax:
FILTER(range, condition1)To filter using the REGEXMATCH formula as the FILTER condition, follow this example:
=Filter(A2:B,ArrayFormula(if(len(A2:A)=0,,REGEXMATCH(A2:A,"^"&textjoin("$|^",true,D1:D)&"$"))))Please note that you can remove the Arrayformula( and one of the closing brackets at the end. The FILTER formula in Google Sheets doesn’t require the ARRAYFORMULA function within.
Regular Expression Match in Query
If your goal is to filter or filter and aggregate data, then QUERY is the better option for exact match using regular expressions in Google Sheets.
To use the MATCHES clause in QUERY for regular expression match of single or multiple criteria, follow these examples:
=query(A2:B,"Select A,B where A matches 'Product 1'")=query(A2:B,"Select A,B where A matches '"&D1&"'")Unlike REGEXMATCH, the “^” and “$” signs around the criterion are not required. However, you can use the “|” separator for multiple criteria.
=query(A1:B,"Select A,B where A matches 'Product 1|Product 22|Product 2'")=query(A1:B,"Select A,B where A matches '"&TEXTJOIN("|",true,D1:D)&"'")For more details about QUERY regular expression match, please check out the Matches Regular Expression Match in Google Sheets Query tutorial.
If you don’t need an exact match, you can explore other options like CONTAINS substring match in Query for a maximum of 2-3 criteria, or MATCHES for several criteria as explained in my post Multiple CONTAINS in WHERE Clause in Google Sheets Query.
That’s all about the exact match using regular expressions in Google Sheets. I hope you found these secrets helpful and can now enjoy a more efficient and accurate data analysis experience. Happy matching!
Learn more about data analysis with regular expressions on Crawlan.com
