Shakil Ahmed
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.
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?
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.
- 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(3 or More Ways)
- Use VLOOKUP to Find Partial Text from a Single Cell
- How to Perform Partial Match String in Excel (5 Methods)
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: COUNTIF Partial Match in Excel (2 or More Approaches)
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.
[email protected](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?
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.
