Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.

Dataset - Excel Partial Match Two Columns

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.


Download Practice Workbook

You can download the practice workbook from here.


4 Easy Methods to Find Partial Match in Two Columns

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.
=IFERROR(VLOOKUP("*"&C5&"*";$B$5:$B$12;1;0);"")

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

VLOOKUP formula to column partial match - Excel Partial Match Two Columns

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.

VLOOKUP formula result

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

Final Result Excel Partial Match Two Columns

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.


🔎 How Does the Formula Work?

  • VLOOKUP(“*”&C5&”*”;$B$5:$B$12;1;0): In the first portion, we will find the desired cell ranges between cell B5 to B12 to find particular values.
  • IFERROR(VLOOKUP(“*”&C5&”*”;$B$5:$B$12;1;0);” ): This portion will apply the proper criteria in the formula to show the final result according to the desired cell range.

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

Read More: Excel VLOOKUP to Find the Closest Match (with 5 Examples)


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.
=IFERROR(INDEX($B$5:$B$12;MATCH("*"&C5&"*";$B$5:$B$12;0));"")

INDEX - MATCH formula - Excel Partial Match Two Columns

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

Results for Excel Partial Match Two Columns

  • Lastly, you will get your final result.

 🔎 How Does the Formula Work?
  • MATCH(“*”&C5&”*”;$B$5:$B$12;0): In the first portion, we will find the desired cell ranges we want to use.
  • INDEX($B$5:$B$12; MATCH(“*”&C5&”*”;$B$5:$B$12;0)): When you intend to return a value (or values) from a single range, you will use the array form of the INDEX function. This portion will apply the proper criteria in the formula.
  • IFERROR(INDEX($B$5:$B$12; MATCH(“*”&C5&”*”;$B$5:$B$12;0));””): This will take the ranges from the INDEX and MATCH function portion and set the proper condition for the formula.

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.

Read More: Conditional Formatting for Partial Text Match in Excel (9 Examples)


Similar Readings


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.
=IF(COUNTIFS($B$5:$B$12;"*"&C5)=1;"Full Name Is Found";"Full Name not Found")

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

IF-COUNTIF formula AutoFill

  • 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.

 🔎 How Does the Formula Work?

  • COUNTIFS($B$5:$B$12;”*”&C5): In the first portion, we will find the range of the cells which we want to check with the condition.
  • IF(COUNTIFS($B$5:$B$12;”*”&C5)=1; “Full Name Is Found”; “Full Name not Found”): This portion will apply the proper criteria in the formula.

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

Read More: COUNTIF Partial Match in Excel (2 or More Approaches)


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 SUMCOUNTLARGE and MAX won’t function if a range contains errors. However, you can quickly solve this by using the AGGREGATE function. This article will show you how to aggregate data in Excel.

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:

Function Function_number
AVERAGE 1
COUNT 2
CONTACT 3
MAX 4
MIN 5
PRODUCT 6
SUM 9
LARGE 14
SMALL 15

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

  • First, insert the following formula in the cell.
[email protected](E$5:E$8;AGGREGATE(15;6;MATCH("*"&$E$5:$E$8&"*";$B5;0)*(ROW($E$5:$E$8)-ROW(E$5)+1);1))

AGGREGATE Formula - Excel Partial Match Two Columns

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

AGGREGATE Formula - Excel Partial Match Two Columns

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

Results for AGGREGATE Formula - Excel Partial Match Two Columns


🔎 How Does the Formula Work?

  • (ROW($E$5:$E$8)-ROW(E$5)+1): When you have a tiny dataset, it’s easy to find the row number but in the case of a larger dataset, you may have to use the ROW function. In the first portion, we will find the desired cell ranges we want to use.
  • MATCH(“*”&$E$5:$E$8&”*”;$B5;0): Whenever you work on an Excel worksheet, you may want to find a relationship between two or more cells. Suppose you want to match criteria with other cells. In this case, you may use the MATCH function. This portion will try to find the match within the selected range.
  • AGGREGATE(15;6; MATCH(“*”&$E$5:$E$8&”*”;$B5;0)*(ROW($E$5:$E$8)-ROW(E$5)+1): In Excel, the AGGREGATE function is used on different functions to get specific results.  In this case, you may use the MATCH function. This portion will apply the proper criteria in the formula.
  • @INDEX(E$5:E$8;AGGREGATE(15;6;MATCH(“*”&$E$5:$E$8&”*”;$B5;0)*(ROW($E$5:$E$8)-ROW(E$5)+1);1)): When you intend to return a value (or values) from multiple ranges, you will use the reference form of the INDEX function. This portion will return you the final result accordingly.

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

Read More: Lookup Partial Text Match in Excel (5 Methods)


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.

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 which we might have missed here.


Further Readings

Shakil Ahmed

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo