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.

**Download Practice Workbook**

You can download the practice workbook from here.

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