Table of Contents
You can use the following syntax to find partial matches between two columns in Excel:
=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 in Two Columns in Excel
Suppose we have one column in Excel that shows the full team names of various basketball teams and another column that shows abbreviated names of various basketball 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.