Excel: How to Find Partial Match in Two Columns

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:

Excel partial match with two columns

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.

For example, we can see:

  • 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 operations in Excel:

Excel: Count Number of Matches Between Two Columns
Excel: How to Find All Values that Match Criteria
Excel: How to Find Matching Values in Two Worksheets

Featured Posts

Leave a Reply

Your email address will not be published. Required fields are marked *