How to Perform Partial Match of String in Excel (8 Easy Methods)

Whenever you are handling a wide range of data in your worksheet, partial matching or fuzzy matching is an effective way to find your match quickly. Furthermore, if you want to perform a partial matching string, the most straightforward solution is to use Wildcards. Additionally, Excel has many options like VLOOKUP, XLOOKUP, INDEX with MATCH, combining IF with other functions to perform this task. Today we will learn how to Perform Partial Match String in Excel.

Download Practice Workbook

Download this practice sheet to practice the task while you are reading this article.

8 Methods to Perform Partial Match of String in Excel

Actually, partial match string in Excel can be done in many ways by using a single function or multiple functions simultaneously. In this article, we will learn 8 different methods to do it. Below, we’re going to demonstrate these methods with detailed steps.

1. Employing IF & OR Statements to Perform Partial Match of String

The “IF” function does not support wildcard characters. However, the combination of the IF with other functions can be used to perform a partial match string. Now, let’s learn.

Here, in the following example, we have a data table where the names of some candidates are given in the “Name” column. Now, we need to identify the names that contain one of the text strings given in columns 2 and 3. That means we need to find out the names that include the letter “A” or “L”.

Steps:

• First, on the “Status” column in cell “E5”, apply the IF, OR formula.

Basically, the format of this formula is,

Now, insert the values into the formula. So, the final formula for the partial match is:

• Then, press ENTER, and the formula will identify the partial match string.

• Now apply this formula to the rest of the cells to get the final result. Or you can drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells.

Lastly, you will get all the partial matches.

2. Use of IF, ISNUMBER, and SEARCH Functions for Partial Match of String

Again, we can find out the results containing partial match strings by using the combination of IF, ISNUMBER, and SEARCH functions in Excel.

Here, consider a data set containing the column “Name”, “Match String”, and “Status”. We need to identify the names that contain the partial match string from the column “Match String”.

• Now, apply the formula with the IF, ISNUMBER, and SEARCH functions in the “Status” column in cell D5.

Here, the format is,

• So, you should insert the values. The final formula for the partial match string is

• Then, press ENTER.

Lastly, our result is achieved.

• Lastly, apply this formula for all the cells in the column to find out all the results that contain a partial match string.

3. Using VLOOKUP Function to Perform Partial Match of String

Here, in this section, we will now use the VLOOKUP function to perform a partial match of the string.

Now, let’s consider a table where the names of some candidates and their ranks are given.

• Firstly, copy the column heads and paste them somewhere in the worksheets. And we will perform the task there.

• Then, apply the VLOOKUP function in the F5 cell. The formula is

• Then, press ENTER.

As a result, the formula has performed the partial match string.

• Now, apply the same formula 2 or more times to master this function.

Finally, you will get all the partial matches.

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

4. Incorporating XLOOKUP Function to Perform Partial Match

The XLOOKUP with ISNUMBER can also complete a partial match string in Excel. Now, let’s see the following examples.

In the following example, two tables are given. In the first table, the partial match strings are given with rank. Now, we need to identify the names in the second table that contains the partial match strings and then return the rank associated with those names.

• Now, in cell F5, apply the formula.

Here, the format of this formula is,

• So, you should insert the values in the formula.

• Then, press ENTER.

Finally, the formula successfully returns the rank to the name that contains the partial match strings.

• Then, do the same for all the cells.

As a result, you will see all the matches.

5. Using INDEX Function with MATCH Function to Perform Partial Match of String

Here, we can return the text, that contains the partial match string, using the INDEX with MATCH function in Excel.

Now, see the following example where two tables are given. In the first table, the “Name” and “Rank” of some candidates are given. In the second table, a partial match string is given. At this time, we need to identify the names from the first table that contains the partial match strings.

• Now, in column F5, apply the INDEX with the MATCH formula. The formula is,

• Then, press ENTER.

As a result, we have got the Name “Robben” which contains the partial match string (Rob).

Furthermore, the Asterisk(*) can be used on both sides of the cell if you have characters on both sides of your partial match string. Consider, we have a partial match string “ni”. It has wildcard characters on both sides now we will use this asterisk(*) on both sides of the cell.

• So, for your better understanding, use the following formula in the F6 cell.

• Then, press ENTER to get the result.

6. Combined Functions to Perform Partial Match String with Two Columns

You can employ a combination of functions like the IF function, AND function, ISNUMBER function, and SEARCH function to find out a partial match string in Excel. Furthermore, you can modify these functions for different types of results according to your preference. Now, follow the example given below. Where we have two criteria. So, based on both criteria, we have to extract the partial matches string.

Steps:

• Firstly, you must select a new cell C5 where you want to keep the status.
• Secondly, you should use the formula given below in the C5 cell.

• Finally, press ENTER to get the result.

• Now, drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells.

Lastly, you will find the string which is partially matched.

7. Applying Array Formula to Find Partial Match of String with Two Columns

You can apply an array formula with the combination of some functions like the IF function, COUNT function, and SEARCH function to find out a partial match string in Excel. Furthermore, you can modify these functions for different types of results according to your preference. Now, follow the example given below. Actually, we have two criteria. So, based on both criteria, we have to extract the partial matches string.

Steps:

• Firstly, you have to select a new cell C5 where you want to keep the status.
• Secondly, you should use the formula given below in the C5 cell.

• Finally, press ENTER to get the result.

• Consequently, drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells.

Finally, you will find the string which is partially matched.

How to Get the Position of Partial Match String in Excel

Here, the most interesting part is, you can use only the MATCH function to find out a partial match string in Excel. Now, follow the example given below. Basically, we have criteria. So, based on that criterion, we have to extract the partial matches string from the “Name with Rank” column.

Steps:

• Firstly, you must select a new cell D9 where you want to keep the result.
• Secondly, you should use the formula given below in the D9 cell.

• Finally, press ENTER to get the result.

Read More: How to Use INDEX and Match for Partial Match (2 Ways)

Things to Remember

✅ Here, the XLOOKUP function is only available in Microsoft 365 version. So, only the users of Excel 365 can use this function.

✅Then, the VLOOKUP function always searches for lookup values from the leftmost top column to the right. Moreover, this function “Never” searches for the data on the left.

✅Lastly, the Asterisk(*) is used as a wildcard. So, use it on both sides of the partial match string if you need wildcard characters on both sides.

Practice Section

Now, you can practice the explained method by yourself.

Conclusion

Here, in this article, we discuss how to perform partial match string in Excel using eight different methods. So, hope this article is useful to you when you are facing problems. Also, you are welcome to share your thoughts if you have any confusion.

Further Readings

• Lookup Partial Text Match in Excel (5 Methods)
• Excel Partial Match Two Columns (4 Simple Approaches)
• Partial Match with IF in Excel (4 Basic Operations)
• COUNTIF Partial Match (Couple of Easy Approaches)
• VLOOKUP Partial Text from a Single Cell in Excel