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.

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.

Read More: How to Use VLOOKUP for Partial Match in Excel (4 Ways)

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

The formula will be the following one

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

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.

Dive Deeper: How to Use INDEX and Match for Partial Match (2 Ways)

To know about the functions visit these articles: INDEX, MATCH.

The formula will be the following one

Within the MATCH function, we have compared the values and returned the position when it was 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

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

Read More: COUNTIF Partial Match (Couple of Easy Approaches)

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

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

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.

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

Further Readings

• How to Perform Partial Match String in Excel (5 Methods)
• Lookup Partial Text Match in Excel (5 Methods)
• VLOOKUP Partial Text from a Single Cell in Excel