How to Find Partial Match in Two Columns in Excel (4 Methods)

Depending on the circumstances you may need to compare a couple of columns. The comparison can be done in many forms, one of them being partial matching. Today we are going to show you how to operate the partial match in two columns in Excel.  For this season, we are using Excel Microsoft 365, feel free to use yours.

First things first, let’s get to know about the workbook which is the base of our examples.

Here we have a dataset of a few famous athletes from different sports. Using this dataset we will execute the partial match within two columns. We will use this dataset to explain the methods easily.

How to Find Partial Match in Two Columns in Excel: 4 Easy Methods

1. Partial Match in Two Columns Using VLOOKUP

One of the approaches to perform the partial match between columns is the use of the VLOOKUP function.

The VLOOKUP function finds the data in a range organized vertically.

We will compare the two columns of the above dataset and produce the result in another column.

• First, insert the formula in cell E5.

Here we have set the first row of the Popular Name column at the lookup_value field.

And the Athlete Name column as the lookup_array. Since we need to check partial match we have used the asterisk signs as wildcards. This sign denotes that any number of characters can be there.

• Thereafter, when the match is found the formula will return the full name we selected in the cell.
• Next, use the Fill Handle option to apply the formula to all the cells.

• After that, you will get the final result accordingly.

Note that, in cell E6, you have found a gap as in the C6 cell you have entered the name Dhoni, which the formula can’t find in Column B.

Hence, we have performed the partial match between columns by using the VLOOKUP function in Excel.

2. Partial Match with Combination of INDEX – MATCH Functions

Next, we can use the combination of INDEX and MATCH functions. Earlier section, we have seen how VLOOKUP retrieves the value once it finds the match. Here INDEX – MATCH combination will do the same. MATCH locates the position of a lookup value and INDEX returns the value from the given location.

To know about the functions visit these articles: INDEX, MATCH.

• First, we will insert the formula into cell E5.

• Afterward, you will get results for this cell and then use Fill Handle to apply it to all cells.

• Lastly, you will get your final result.

In this portion, we have used a combination of INDEX and MATCH functions to find partial matches between columns. The IFERROR function ignores any kind of error that may occur because of any inconsistency in the formula.

3. IF Function to Perform Partial Match in Two Columns

In this portion of the article, we can perform the partial match using the IF function. As you know, the IF function runs a logical test and returns a value for a TRUE or FALSE result.

• Now, here we have set the “Full name Is Found” as the if_true_value and left the if_false_value empty. Insert the following formula in the cell.

Here the formula provided the if_true_value. Now write the formula for the rest of the values.

• In addition, after pressing the Enter button you will get the result for this cell and then use the Fill Handle option for all the cells accordingly.

• Finally, you will get the desired result.

Therefore, combining IF and COUNTIF functions to perform the partial match in two columns in Excel is very easy to use.

4. Compare Two Columns Using AGGREGATE Function

Lastly, we will try to find a partial match within two columns by using the AGGREGATE function as well. Microsoft Excel functions like SUM, COUNT, LARGE and MAX won’t function if a range contains errors. However, you can quickly solve this by using the AGGREGATE function.

AGGREGATE Function: Syntax and Arguments

Excel’s AGGREGATE function returns the aggregate of a data table or data list. A function number serves as the first argument, while various data sets make up the other arguments. To know which function to employ, one needs to memorize the function number, or beside you can see it in the table.

Reference and array syntax are the two possible syntaxes for the Excel AGGREGATE function which we will show you here.

Array Syntax:

=AGGREGATE(function_num,options,array,[k])

Reference Syntax:

=AGGREGATE(function_num,options,ref1, [ref2],…)

There is no need to be concerned about the form you are using. Based on the input parameters you supply, Excel will choose the most suitable form.

Arguments:

Now, move on and discuss how to use this function thoroughly by following the below steps.

• First, insert the following formula in the cell.

• Next, you will get the result for this cell and then use the Fill Handle option to apply it to all cells.

• Finally, your screen will show a similar result to the following image.

So, finally, we have concluded our article by using the AGGREGATE function to perform the partial match in two columns in Excel.

Things to Remember

• In the case of using the first two methods, the VLOOKUP and the INDEX-MATCH combinations play the most important roles. When inserting the values try to keep that in mind. If you change the range, the result will be different.
• When using formulas, it is important to insert them with the correct syntax. Otherwise, it won’t give any results.
• We suggest you download the Excel file and see it while using the formulas for better understanding.

Download Practice Workbook

You can download the practice workbook from here.

Conclusion

That’s all for today. We have listed several ways to operate the partial match in two columns in Excel. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know which of the methods you are going to use. Notify any other approaches that we might have missed here.

<< Go Back to Partial Match Excel | Formula List | Learn Excel