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.

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.

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.

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.

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

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.

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

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

Write the formula for the rest of the values.

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

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

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.

Write the formula for the rest of the values.

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

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.

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

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

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.

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

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

Write the formula for the rest of the values.

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