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

Here we have a dataset of a few famous athletes from different sports. Using this dataset, we will find partial matches within two columns.

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

Method 1 – Partial Match in Two Columns Using VLOOKUP

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

• Copy this formula into cell E5:
`=IFERROR(VLOOKUP("*"&C5&"*";\$B\$5:\$B\$12;1;0);"")`

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.

• When the match is found the formula will return the full name we selected in the cell.
• Use the Fill Handle option to apply the formula to all the cells.

• You will get the final result accordingly.

Note that, in cell E6, you have found a gap as the C6 cell contains 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.

Method 2 – Partial Match with Combination of INDEX – MATCH Functions

• Insert this formula into cell E5:
`=IFERROR(INDEX(\$B\$5:\$B\$12;MATCH("*"&C5&"*";\$B\$5:\$B\$12;0));"")`

• Press Enter to get results for the cell and then use the Fill Handle to apply it to all cells.

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

The IFERROR function ignores any kind of error that may occur because of any inconsistency in the formula.

Method 3 – IF Function to Perform Partial Match in Two Columns

• Use the following formula for E5:
`=IF(COUNTIFS(\$B\$5:\$B\$12;"*"&C5)=1;"Full Name Is Found";"Full Name not Found")`

We have set the â€œFull name Is Found” as the if_true_value and left the if_false_value empty.

Here the formula provided the if_true_value.

• After pressing the Enter button, use the Fill Handle option to copy the formula across the column.

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

Method 4 – Compare Two Columns Using AGGREGATE Function

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],â€¦)

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

Steps:

• Insert the following formula in the cell:
`=@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))`

• You will get the result for this cell. Use the Fill Handle option to apply it to all cells.

• Your screen will show a result similarÂ to the following image.

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.

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

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF