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

Get FREE Advanced Excel Exercises with Solutions!

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.

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

=IF(OR(ISNUMBER(SEARCH(text,cell)),ISNUMBER(SEARCH(text,cell))),”value_if_true”, “value_if_false”)

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

`=IF(OR(ISNUMBER(SEARCH(C5,B5)),ISNUMBER(SEARCH(D5,B5))),"YES","NO")` Formula Breakdown

• Here, the Text is C5 (A), D5 (L). The formula will ensure whether C5 or D5 is the partial match string.
• Then, the cell is B5 (Jonathan).
• Value_if_true is “YES”.
• Value_if_false is “NO”.

• 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,

=IF(ISNUMBER(SEARCH(“text”, cell)), value_if_true, value_if_false)
• So, you should insert the values. The final formula for the partial match string is
`=IF(ISNUMBER(SEARCH(C5,B5)),"YES","NOT FOUND")`
• Then, press ENTER.

Lastly, our result is achieved. Formula Breakdown

• Here, the Text is C5 (A). The formula will ensure whether C5 is the partial match string or not.
• Then, the cell is B5 (Jonathan).
• Value_if_true is “YES”.

• 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
`=VLOOKUP(\$E\$5&"*",\$B\$5:\$C\$10,2,FALSE)` Formula Breakdown

• Firstly, Lookup_value is \$E\$5&”*”. Here, we use the Asterisk (*) as a wildcard that matches zero or more text strings.
• Secondly, Table_array is \$B\$5:\$C\$10.
• Thirdly, Col_index_num is 2.
• Fourthly, [range_lookup] is FALSE as we want the exact match.

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

=XLOOKUP(lookup_value,ISNUMBER(SEARCH(text,cell)),return_array)
• So, you should insert the values in the formula.
`=XLOOKUP(TRUE,ISNUMBER(SEARCH(\$B\$5:\$B\$10,E5)),\$C\$5:\$C\$10)`
• Then, press ENTER.

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

• Firstly, lookup_value is “TRUE”.
• Secondly, the text is \$B\$5:\$B\$10.
• Thirdly, the cell is E5 ( Henry Jonathan). And the formula will return the rank for Henry Jonathan.
• Fourthly, return_array is \$C\$5:\$C\$10.

• 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,
`=INDEX(\$B\$5:\$B\$10,MATCH(E5&"*",\$B\$5:\$B\$10,0))`
• Then, press ENTER.

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

• Firstly, the array is \$B\$5:\$B\$10.
• Secondly, lookup_value is E5&”*”. Here, we use the Asterisk (*) as a wildcard that matches zero or more text strings.
• Thirdly, lookup_array is \$B\$5:\$B\$10.
• Fourthly, [match_type] is EXACT (0).

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.
`=INDEX(\$B\$5:\$B\$10,MATCH("*"&E6&"*",\$B\$5:\$B\$10,0))`
• 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.
`=IF(AND(ISNUMBER(SEARCH(\$E\$6, B5)), ISNUMBER(SEARCH(\$F\$6, B5))), "Found", "")`
• Finally, press ENTER to get the result. Formula Breakdown

• Here, SEARCH(\$F\$6, B5) will search if there are any strings Ad in the B5 cell.
• Output: #VALUE!.
• Then, the ISNUMBER function will check whether the above output is a number or not.
• Output: FALSE.
• Similarly, ISNUMBER(SEARCH(\$E\$6, B5)) will do the same operation. Here, the SEARCH function will find 9 in the B5 cell.
• Output: FALSE.
• After that, the AND function will check are both logic is TRUE.
• Output: FALSE.
• Lastly, the IF function will return “Found” if both the previous logic become TRUE. Otherwise, it will return a void cell.
• Output: Here, the output is blank/empty as there is no match for the string value of the B5 cell.

• 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.
`=IF(COUNT(SEARCH({"A","12"}, B5))=2, "Found", "")`
• Finally, press ENTER to get the result. Formula Breakdown

• Here, SEARCH({“A”,”12″}, B5) will search if there are any strings A and the number 12 in the B5 cell.
• Output: {#VALUE!,7}.
• Then, the COUNT function will count the valid cell from the above output.
• Output: 1.
• Lastly, the IF function will return “Found” if both the COUNT function returns 2. Otherwise, it will return a void cell.
• Output: Here, the output is blank/empty as there is no match for the string value of the B5 cell.

• 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.
`=MATCH("*"&D6&"*", B5:B10, 0)`
• Finally, press ENTER to get the result. Formula Breakdown

• Firstly, lookup_value is “*”&D6&”*”. Here, we use the Asterisk (*) as a wildcard that matches zero or more text strings.
• Secondly, lookup_array is B5:B10.
• Thirdly, [match_type] is EXACT (0).

## 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 #### Asikul Himel

Hi! I am Asikul Islam Himel. Glad you are here. I am a Team Leader of ExcelDemy, running an excellent team of five efficient Excel & VBA Content Developers. Here at ExcelDemy, we give the best sustainable solutions by posting articles related to MS Excel-related problems. I have completed my under graduation degree from Bangladesh University of Engineering and Technology and my program was Naval Architecture and Marine Engineering. I have found passion in data analysis and research-based fields. I am currently working to grow my leadership quality. I have a great interest in project management and critical thinking. In my free time, I love to travel and read books.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  