How to Perform Partial Match String in Excel (5 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. If you want to perform a partial matching string, the most straightforward solution is to use wildcards. 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.

Partial Match string in Excel: 5 Methods

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 5 different methods to do it.

1.   IF OR Statements to Perform Partial Match String

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

Step-1:

In the following example, we have a data table where the names of some candidates are given in the “Name” column. 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”.

Step-2:

On the “Status” column in cell “E4”, apply the IF OR formula. The format of this formula is,

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

Insert the values into the formula. The final formula for the partial match is

Where,

• Text is C4 (A),D4 (L) . The formula will ensure whether C4 or D4 is the partial match string.
• The cell is B4 (Jonathan).
• Value_if_true is “YES”.
• Value_if_false is “NO”.

Press “Enter”. The formula has identified the partial match string.

Step-3:

Now apply this formula to the rest of the cells to get the final result.

2. IF ISNUMBER SEARCH Formula for Partial Match String

We can find out the results containing partial match strings by using the IF ISNUMBER SEARCH combo.

Step-1:

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

Step-2:

Apply the IF ISNUMBER SEARCH formula in the “Status” column in cell D4

The format is,

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

Insert the values. The final formula for the partial match string is

Where,

• Text is C4 (A). The formula will ensure whether C4 is the partial match string or not.
• The cell is B4 (Jonathan).
• Value_if_true is “YES”.
• Value_if_false is “NOT FOUND”.

Press “Enter”. Our result is achieved.

Step-3:

Now apply this formula for all the cells in the column to find out all the results that contain partial match string.

3. VLOOKUP to Perform Partial Match String

We will now use the VLOOKUP function to perform a partial match of the string.

Related Content: VLOOKUP Partial Text from a Single Cell in Excel

Step-1:

Consider a table where the names of some candidates and their ranks are given.

Step-2:

Copy the column heads and paste them somewhere in the worksheets. We will perform our task there.

Step-3:

Apply the VLOOKUP function in the F4 cell. The formula is

Where,

• Lookup_value is $E$5&”*”. We use the Asterisk (*) as a wildcard that matches zero or more text strings.
• Table_array is $B$4:$C$9.
• Col_index_num is
• [range_lookup]: we want the exact match (FALSE)

Press “Enter”. The formula has performed the partial match string.

Step-4:

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

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

4. XLOOKUP to Perform Partial Match String

The XLOOKUP with ISNUMBER can also complete partial match string

Step-1:

In the following example, two tables are given. In the first table, the partial match strings are given with rank. 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.

Step-2:
In cell F4, apply the formula. The format of this formula is,

=XLOOKUP(lookup_value,ISNUMBER(SEARCH(text,cell)),return_array)

Insert the values in the formula.

Where,

• Lookup_value is “TRUE”.
• Text is $B$4:$B$9.
• The cell is E4 ( Henry Jonathan). The formula will return the rank for Henry Jonathan
• Return_array is $C$4:$C$9.

Press “Enter”. The formula successfully returns the rank to the name that contains the partial match strings.

Step-3:

Now do the same for all the cells.

5. INDEX with MATCH to Perform Partial Match String

Using the INDEX with MATCH function we can return the text that contains the partial match string.

Step-1:

In the following example, 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. We need to identify the names in the first table that contains the partial match strings.

Step-2:

In column F4, apply the INDEX with the MATCH formula. The formula is,

Where,

• The array is $B$4:$B$9.
• Lookup_value is E4&”*”. We use the Asterisk (*) as a wildcard that matches zero or more text strings.
• Lookup_array is $B$4:$B$9.
• [match_type] is EXACT (0)

Press “Enter”. We have got the Name “Robben” that contains the partial match string (Rob)

Step-3:

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 the formula will be,

=INDEX($B$4:$B$9,MATCH(“*”&E5&”*”,$B$4:$B$9,0))

Press “Enter”. Our result is here.

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

Quick Notes

✅ The XLOOKUP function is only available in Excel 365. Only the users of Excel 365 can use this function.

✅The VLOOKUP function always searches for lookup values from the leftmost top column to the right. This function “Never” searches for the data on the left.

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

Conclusion

Performing partial match string in excel is discussed in this article using five different methods. Hope this article is useful to you when you are facing problems. 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)