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.

**Table of Contents**hide

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

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

**🔎 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));"")`

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

**🔎 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**

**How to Use Partial VLOOKUP in Excel (5 Suitable Examples)****Use VLOOKUP to Find Partial Text from a Single Cell****Perform Partial Match of String in Excel (8 Easy Methods)****How to Use VLOOKUP to Find Partial Text from a Single Cell**

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

- 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:** **How to Use COUNTIF Function for Partial Match in Excel**

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

`=@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))`

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

**🔎 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.