Table of Contents
To find a partial match in two columns using Google Sheets, you can use the “COUNTIF” function. First, select the cells where you want the results to appear. Then, type “=COUNTIF(range1, “*”&range2&”*”)” in the formula bar, replacing “range1” and “range2” with the columns you want to compare. This formula will count the number of cells in range1 that contain any text from range2. If the result is greater than 0, it means there is a partial match. You can then use conditional formatting to highlight the cells with a partial match for easier identification.
You can use the following syntax to find partial matches between two columns in Google Sheets:
=IFERROR(VLOOKUP("*"&B2&"*", $A$2:$A$9, 1, 0), "")
This particular formula will check if the value in cell B2 has a partial match with any cell in the range A2:A9.
If a partial match is found, then the value in the range A2:A9 is returned.
If no match is found, then a blank is returned.
Note: We used asterisks ( * ) around cell B2 as wildcards to indicate that there may be text before or after the text in cell B2 within the range A2:A9.
The following example shows how to use this formula in practice.
Example: Find Partial Match Between Two Columns in Google Sheets
Suppose we have one column in Google Sheets that shows the full team names of various basketball teams and another column that shows abbreviated names of various teams:
Suppose we would like to check if each team name in the abbreviated column can be partially matched to a team in the full team name column.
We can type the following formula into cell C2 to do so:
=IFERROR(VLOOKUP("*"&B2&"*", $A$2:$A$9, 1, 0), "")
We can then click and drag this formula down to each remaining cell in column C:
If the team name in column B has a partial match with any team name in column A, then the full team name from column A is returned in column C.
However, if no partial matches are found then a blank is returned.
- The “Rockets” had a partial match with “Houston Rockets”
- The “Spurs” had a partial match with “San Antonio Spurs”
- The “Lakers” had no partial match, so a blank was returned.
And so on.
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets: