Excel Partial Match Two Columns (4 Simple Approaches)

Depending on the circumstances you may need to compare a couple of columns. The comparison can be done in many forms, one of them is partial matching. Today we are going to show you how to operate the partial match between two columns.  For this season, we are using Excel 2019, 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.

Note that this is a basic dataset to keep things simple. In a practical scenario, you may encounter a much larger and complex dataset.

Practice Workbook

You are welcome to download the practice workbook from the link below.

Partial Match within Two Columns

1. Partial Match with VLOOKUP

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

The VLOOKUP function looks up the data in a range organized vertically. To know more about the function, visit the VLOOKUP article.

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

Compare column - Excel Partial Match Two Columns

The formula will be the following one

=VLOOKUP("*"&C4&"*",$B$4:$B$11,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.

When the match is found the formula will return the full name as we selected in the formula.

VLOOKUP formula result - Excel Partial Match Two Columns

But when the lookup_value is not found, then the formula will return an error.

Error return in VLOOKUP formula - Excel Partial Match Two Columns

Here we have found #N/A for the lookup_value “Dhoni” which is not found within the range.

To rectify the issue we need to use a function called IFERROR. To know about the function, visit the IFERROR article.

Now let’s rewrite the earlier formula wrapping by IFERROR

=IFERROR(VLOOKUP("*"&C4&"*",$B$4:$B$11,1,0),"")

Here we have set the VLOOKUP function at the value field at the IFERROR function.

IFERROR- VLOOKUP formula - Excel Partial Match Two Columns

Here we leave the if_error field empty. When there will be no match, then the formula will leave the resultant cell empty.

IFERROR- VLOOKUP formula result - Excel Partial Match Two Columns

Now notice that for a not matched value the formula will return empty.

IFERROR- VLOOKUP formula result 2 - Excel Partial Match Two Columns

Write the formula for the rest of the values.

IFERROR- VLOOKUP formula AutoFill - Excel Partial Match Two Columns

2. Partial Match with INDEX – MATCH Combination

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.

The formula will be the following one

=IFERROR(INDEX($B$4:$B$11,MATCH("*"&C4&"*",$B$4:$B$11,0)),"")

Within the MATCH function, we have compared the values and returned the position when it found.

INDEX - MATCH formula - Excel Partial Match Two Columns

Then the INDEX function returns the value according to the location number.

INDEX - MATCH formula result - Excel Partial Match Two Columns

We have written the formula within the IFERROR function. Do you identify the reason?

Yes, to eradicate any error that arises when the match is not found.

INDEX - MATCH formula result 2- Excel Partial Match Two Columns

Write the formula for the rest of the values.

INDEX - MATCH formula AutoFill - Excel Partial Match Two Columns

3. IF Function to Perform Partial Match

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. Feel free to check this IF article.

As mere IF can not work with wildcards like an asterisk (*), it needs assistance from other functions like COUNTIF. To know about the function, visit the COUNTIF article.

Let’s write the formula first

 =IF(COUNTIF($B$4:$B$11,"*"&C4&"*"),"Full name Is Found","")

Within the COUNTIF function, we set the range and the criteria with wildcards. And it will count when it will find the match.

IF-COUNTIF formula - Excel Partial Match Two Columns

As long as the COUNTIF function returns a value greater than 0, IF will recognize that as TRUE, and FALSE otherwise.

Here we have set the “Full name Is Found” as the if_true_value and leave the if_false_value empty.

IF-COUNTIF formula result - Excel Partial Match Two Columns

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

IF-COUNTIF formula AutoFill- Excel Partial Match Two Columns

4. Compare Two Columns using AGGREGATE

The partial match operation between two columns can be done by the AGGREGATE function as well. In this section, we will see how to match the sports category for the respective athletes by a partial match.

AGGREGATE example data - Excel Partial Match Two Columns

Let’s tell you a bit about the AGGREGATE function. The AGGREGATE function returns an aggregate calculation like AVERAGE, COUNT, MAX, etc.

The syntax for the AGGREGATE function is as follows

AGGREGATE(function_number,behavior_options, range)

function_number: This number specifies which calculation should be made.

behavior_options: Set this using number. This number denotes how the function will behave.

range: Range you want to aggregate.

The AGGREGATE function does several tasks so numbers of functions are predefined within it. We are listing few frequently used function numbers

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

To know more about the function, visit the Microsoft Support site.

Now let’s see the formula, our formula will also have INDEX, MATCH, and ROW.

=INDEX(E$4:E$7,AGGREGATE(15,6,MATCH("*"&$E$4:$E$7&"*",$B4,0)*(ROW($E$4:$E$7)-ROW(E$4)+1),1))

E4:E7 is the array within which we need to find.  Within AGGREGATE we have set 15 as function_number and 6 as behavior_option that stands for ignoring error values.

And the ROW functions provide the row number iteratively.

AGGREGATE Formula - Excel Partial Match Two Columns

The AGGREGATE portion of the formula provides the location num getting the assistance from the MATCH function. And then INDEX returns the value.

AGGREGATE Formula result - Excel Partial Match Two Columns

When it finds the Scorer it returns the scorer category (above image) and when it finds Cricketer then it returns the perfect category (image below)

AGGREGATE Formula result 2 - Excel Partial Match Two Columns

Write the formula for the rest of the values.

AGGREGATE Formula AutoFill - Excel Partial Match Two Columns

Conclusion

That’s all for today. We have listed several ways to operate the partial match between two columns. 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.

shakil

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