How to filter an array with another array in Sheets. This is what you want to know to filter out matching Keywords in Google Sheets. For this, you can choose between Query and Filter. I prefer the Filter function here as it seems simple to code.

This post is about advanced keyword filtering using Sheets, Google’s trending Spreadsheet solution. Let’s begin with understanding the problem.

In my Sheet, column A contains a long list of keywords. In column C, I have another list of keywords. For explanation, we can give a title to the column A keywords as total_keywords and column C keywords as used_keywords.

I want a formula in cell E1 that can filter the keywords in column A that don’t exist in column C. It can be a full match or partial match. In precise I want to remove used keywords in a list whether it’s a full match or partial match.

Partial Match and Full/Exact Match of Keywords in Sheets

If ‘natural beauty tips’ is one of the keywords under the total_keywords list in column A and ‘beauty’ is under used_keywords in column C, it partially matches. So the formula must remove the former keyword from the filtered output of column A.

That’s the partial match of keywords that I am going to talk in Sheets. What about the full/exact match of keywords?

If you take the same keywords, in this new scenario the keyword ‘natural beauty tips’ must be extracted. Both are, I mean ‘natural beauty tips’ and ‘beauty’ are different in terms of an exact match.

You can learn in this tutorial how to filter out matching (full/partial) keywords in Google Sheets. If you are looking for the opposite of this, please do check my tutorial – Filter Based on a List in Another Tab in Google Sheets. Also, you can use the same formulas below by only changing the Boolean value FALSE to TRUE.

How to Filter Out Partial Matching Keywords in Google Sheets

Here is the screenshot of what I am talking about. In this example, in column E, I have filtered out or you can say removed all the partial matching keywords in column A.

Formula:

={"balance_keywords";filter(A2:A,regexmatch(A2:A,textjoin("|",1,C2:C))=FALSE)}

Formula Explanation:

Using the Filter function, I am filtering column A. Let me elaborate on the conditions/criteria used for filtering, which is a Regexmatch formula (regular expression match).

The Textjoin function acts as the regular_expression in the Regexmatch as below.

Texjoin Output:

cosmetics|beauty|apple

The Regexmatch returns TRUE for the partial match of the keywords separated by the pipe symbol and FALSE for the mismatch.

I have filtered the FALSE (mismatch) part that virtually means I have filtered out the TRUE (partial match) part.

How to Filter Out Fully Matching Keywords in Google Sheets

Here also first I am going to provide you the formula and the output first.

Formula:

={"balance_keywords";FILTER(A2:A,regexmatch(A2:A,textjoin("|",1,("^"&filter(C2:C,C2:C<>"")&"$")))=FALSE)}

Formula Explanation:

Here we are filtering out keywords that are fully/exactly matching. Compared to the first formula, here the change is in the Regexmatch regular_expression using the Textjoin.

We must use the regular_expression in Regexmatch as this for an exact match.

^cosmetics$|^beauty$|^apple$

In Regexmatch, we can use the ^ to match the beginning (empty string) of a text and the $ to match the end of the text.

Simply joining the above signs as below won’t return the required output.

=ArrayFormula(textjoin("|",1,("^"&C2:C&"$")))

If you use the above Regexmatch, the output will be as follows which is not a correct regular_expression.

^cosmetics$|^beauty$|^apple$|^$|^$|^$|^$|^$|^$|^$|^$|^$|^$

The reason for the extra pipe symbols and the empty strings is because of the extra blank rows in the criteria field (column C).

If you use C2:C5 instead of C2:C then the formula would return the proper regular_expression. But I want to use C2:C. So the option here is to filter out the blank rows.

=ArrayFormula(textjoin("|",1,("^"&filter(C2:C,C2:C<>"")&"$")))

In the final formula, we can omit the ArrayFormula. It’s required only in standalone use as above, I mean not required inside a Filter (final formula). Hope this makes sense.

This way you can filter out matching keywords in Google Sheets. Any doubt? Please ask me in the comments.

Additional Resources

• Filter Values Between Two Group Headers (Titles) in Google Sheets.
• Formula to Extract Listed Keywords from Titles in Google Sheets.
• Formula to Conditionally Filter Last N Rows in Google Sheets.
• Comma-Separated Values as Criteria in Filter Function in Google Sheets.
• How to Filter Top 10 Items in Google Sheets Pivot Table.
• Two-way Filter in Google Sheets [Dynamic Vertical and Horizontal Filter].
• The Alternative to SQL IN Operator in Google Sheets Query (Also Not IN) – MUST READ.
• Using Keyword Combinations in Vlookup in Sheets.