Formula to Find Partial Match in Two Columns in Google Sheets

You should take the topic Partial Match in two columns in Google Sheets in a much broader sense. You know why?

Honestly, I myself thought it would be easy to do a two column match. But later when I did some testing, I realized that it’s not so easy to find the partial match in two columns in Google Sheets. The reason, I had to consider different things before going for a formula. Like;

Different Scenarios in Partial Match in two columns in Google Sheets

Partial Match Scenario 1:

1. Partial match two columns row-wise in Google Sheets.

I want to compare Column B with Column A for any match, whether it’s full or partial, in the same row in Column A, that in all rows.

Partial Match Scenario 2:

2. Partial match two columns that not in order.

In this, I want to check Column B for any match, whether it’s full or partial in any row in Column A. It can be multiple partial matches in different cells in Column A.

What’re multiple partial matches in columns?

Sometimes a value in Column B can be found multiple times in Column A or vice versa. In such cases also, I want to find the match.

If there are no multiple matches or partial multiple matches, there is a pretty easy solution using Vlookup. That Google Sheets tutorial, I’ve shared at the end of this post.

Partial Match Scenario 3:

3. Additionally, you should know how to exclude blank rows in a partial match in two columns in Google Sheets. Lots of complications surrounding, right?

Needless to say, you can only find the partial match in two columns in Google Sheets only with Text values in columns.

If the values in the related columns are numbers, you can just go with the full match not partial.

So I’m going to provide you with three different formulas here. The below image explains what the formulas are meant for. Please see the formula results in Column D, E, and F.

Google Sheets: Partial Match in Columns Example

How to Find Partial Match in Two Columns in Google Sheets

Hope you have taken the time to understand the above data. In Column D, E and F, I’m comparing the text values in Column B with Column A. So let’s begin with the formula result in Column D and the formula in Cell D2.

A] Partial Match Two Text Columns

Find Partial Match in Two Columns That Not in Order in Google Sheets

If Column B has any Match (Partial / Full) in Column A, the formula will return “Yes” otherwise “No”

Note: Please refer my Functions Guide (Google Sheets) to Learn the functions used in forming the below formulas.

Formula # 1

=ArrayFormula(if(len(B2:B),if(COUNTIF(A2:A,"*"&B2:B&"*")>=1,"Yes","No"),))

In our example, the value in B2 “Mango” has a match in A3 in Column A. So the formula would return “Yes” in cell D2. It’s a full match.

Now regarding the partial match, see the Cell B7 which has the word “Lemon”. This word has a match in Cell A7.

Here I’ve made the use of Google Sheets Asterisk Wildcard Character for partial match.

How to Do a Partial Match Between Two Columns in Row-Wise in Google Sheets

In our sample sheet, Column E addresses this issue. The below formula can help you to find the partial match in two columns row-wise.

Formula # 2

=ArrayFormula(iferror(if(len(B2:B),if(search(B2:B,A2:A)>0,"Yes"),),"No"
))

This’s the formula that I’ve keyed in Cell E2 in my sample sheet. As you can see, the Cell B6 and B7 have only the partial match in the same row in my sample sheet. So it returns “Yes” only in Cell E6 and E7.

B] Partial Match Two Numeric Columns

Unlike the previous formula # 1, you can use the above formula # 2 to find matching numeric values in the same row. Then is there any alternative to our first formula for columns with numeric values.

Here it is (formula in the Cell F2).

Formula # 3

=ArrayFormula((if(len(B2:B),if(countif(A2:A,B2:B)>=1,"Yes","No"),)))

In a numeric column, there is no point in a partial match. So this formula will work just fine. That’s all about finding a partial match in two columns in Google Sheets.

Similar:

1. How to Compare Two Columns for Matching Values in Google Sheets

2. How to Find Multiple Occurrences of Rows in Google Sheets [Duplicates]

3. Remove Duplicates in Google Sheets [The Complete Guide and Sample Sheet]

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

11 COMMENTS

  1. Hi,

    I want to have in C or any other column, a clean list of Column A filtered out with the text in B column.

    Thanks (my current method is making helper columns, copy, and paste, etc.)

  2. Hello. This formula seems to be exactly what I need. However, it is not capturing partial matches. So, if column A includes “orange” and B contains “orange juice” the response is “No”. My understanding is that it would be “Yes” because it does contain “orange”.

    =ArrayFormula(if(len(B2:B),if(COUNTIF(A2:A,"*"&B2:B&"*")>=1,"Yes","No"),))

    Also, would it match “oranges” which include “orange” within the larger plural word?

    Thank you!

      • Hello. Thank you for the quick response. Unfortunately, this does not work at all. Yours is the only website I’ve found that comes close to answering my question. I appreciate your help!

      • HI again. A friend came up with this. Works like a charm:

        =filter(Example!B2:B,regexmatch(Example!B2:B,textjoin("|",1,Example!A2:A))=FALSE)

        • Hi, Jason,

          Sorry! My answer was without seeing your data.

          Please do a search for the key “regexmatch” on this site. You can use the search icon on the top (navigation/menu bar).

          Cheers!

  3. Search for a Google Sheet add-on called Flookup. It’s patterned after VLOOKUP with a specific focus on fuzzy or approximate matching… and it’s free

  4. Hello.

    I sincerely appreciate your lectures. However, I am having a challenge with your formula in (A) above. I keep getting the error:#NAME?.

    The figures is more or less the same as in column D in the 2nd excel example above. Please, how do I fix this?

    Thanks

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.