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:
=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.
Read More: How to Find Partial Match in Two Columns in Excel (4 Methods)
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
=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”.
- Value_if_false is “NOT FOUND”.
- 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.
Read More: How to Use VLOOKUP for Partial Match in Excel (4 Suitable Ways)
Similar Readings
- How to Use VLOOKUP to Find Approximate Match for Text in Excel
- Fuzzy Lookup in Excel (With Add-In & Power Query)
- Excel VLOOKUP to Find the Closest Match (with 5 Examples)
- How to Use Partial VLOOKUP in Excel (5 Suitable Examples)
- Excel SUMIF with Partial Match (3 Easy 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.
=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.
Read More: Lookup Partial Text Match in Excel (5 Methods)
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.
Read More: How to Use IF Function to Find Partial Match in Excel (6 Ways)
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).
Read More: How to Use INDEX and Match for Partial Match (2 Easy 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
- How to Use COUNTIF Function for Partial Match in Excel
- Conditional Formatting for Partial Text Match in Excel (9 Examples)
- How to Use VLOOKUP to Find Partial Text from a Single Cell
- Highlight Partial Text in Excel Cell (9 Methods)
- How to Use Formula for Partial Number Match in Excel (5 Examples)
