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.
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]
Thank you.
This solution works like a charm.
Much obliged.
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.)
Hi, SPH,
This may help – Filter Out Matching Keywords in Google Sheets – Partial or Full Match.
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!
Hi, Jason,
Maybe this.
=ArrayFormula(if(len(A2:A),if(COUNTIF(B2:B,"*"&A2:A&"*")>=1,"Yes","No"),))
Best,
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!
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
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
Hi Hakeem,
Please leave a demo sheet.