Filter Out Matching Keywords in Google Sheets – Partial or Full Match

Are you struggling to filter out matching keywords in Google Sheets? Look no further! In this article, I’ll show you how to use the Filter function to easily accomplish this task. Let’s dive right in!

Partial Match and Full/Exact Match of Keywords in Sheets

Let’s start by understanding the difference between a partial match and a full/exact match of keywords. Imagine you have two lists: total_keywords in column A and used_keywords in column C. If the keyword “natural beauty tips” is present in both lists, it partially matches with the keyword “beauty” in column C. In this case, we want to remove the keyword “natural beauty tips” from the filtered output of column A.

On the other hand, if we’re looking for a full/exact match, the keyword “natural beauty tips” should be extracted because it doesn’t match exactly with the keyword “beauty.”

To learn how to filter out matching (full/partial) keywords in Google Sheets, keep reading! And if you’re interested in the opposite scenario, check out my tutorial on how to filter based on a list in another tab in Google Sheets.

How to Filter Out Partial Matching Keywords in Google Sheets

Let’s take a look at an example. In the screenshot below, I have filtered out all the partial matching keywords in column A and displayed the result in column E.

The formula to achieve this is:

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

Here’s how the formula works:

1. The textjoin("|",1,C2:C) function combines all the keywords in column C separated by the pipe symbol (|).
2. The regexmatch(A2:A,textjoin("|",1,C2:C))=FALSE condition checks if each keyword in column A is not a partial match.
3. The filter(A2:A,regexmatch(A2:A,textjoin("|",1,C2:C))=FALSE) function filters out the keywords that don’t match partially.

By using this formula, you can easily filter out partial matching keywords in Google Sheets.

How to Filter Out Fully Matching Keywords in Google Sheets

Now let’s move on to filtering out fully matching keywords. Take a look at the example below, where I have filtered out the fully matching keywords in column A and displayed the result in column G.

The formula to achieve this is:

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

Here’s how the formula works:

1. The ("^"&filter(C2:C,C2:C<>"")&"$") condition creates a regular expression pattern for exact matching keywords. The filter(C2:C,C2:C<>"") function removes any empty cells from column C and the ^ matches the beginning of the text, while the $ matches the end.
2. The regexmatch(A2:A,textjoin("|",1,("^"&filter(C2:C,C2:C<>"")&"$")))=FALSE condition checks if each keyword in column A doesn’t match exactly.
3. The FILTER(A2:A,regexmatch(A2:A,textjoin("|",1,("^"&filter(C2:C,C2:C<>"")&"$")))=FALSE) function filters out the fully matching keywords.

By using this formula, you can easily filter out fully matching keywords in Google Sheets.

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

I hope this article has been helpful in understanding how to filter out matching keywords in Google Sheets. If you have any doubts or questions, feel free to ask in the comments. Happy filtering!