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:
- 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:
- 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:
- 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 to 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 a 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:
