Google Sheets: How to Find Partial Match in Two Columns


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:

Google Sheets partial match between 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 tasks in Google Sheets:

Google Sheets: Use VLOOKUP to Return Last Matching Value
Google Sheets: How to Return Column Number of Match
Google Sheets: How to Sort Two Columns to Match

Leave a Reply

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