How to Perform Partial Match String in Excel (5 Methods)

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

Using IF OR Formula to perform partial match string

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

=IF(OR(ISNUMBER(SEARCH(C4,B4)),ISNUMBER(SEARCH(D4,B4))),”YES”,”NO”)

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

Using IF OR Formula to perform partial match string

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

Using IF OR Formula to perform partial match string

Step-3:

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

Using IF OR Formula to perform partial match string

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

creating table

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

=IF(ISNUMBER(SEARCH(C4,B4)),”YES”,”NOT FOUND”)

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

applying formula

Press “Enter”. Our result is achieved.

result

Step-3:

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

final result

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.

creating table

Step-2:

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

Using VLOOKUP to perform partial match string

Step-3:

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

=VLOOKUP($E$5&”*”,$B$4:$C$9,2,FALSE)

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)

Using VLOOKUP to perform partial match string

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

Using VLOOKUP to perform partial match string

Step-4:

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

final result

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.

creating table

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.

=XLOOKUP(TRUE,ISNUMBER(SEARCH($B$4:$B$9,E4)),$C$4:$C$9)

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.

applying formula

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

result

Step-3:

Now do the same for all the cells.

final result

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.

creating table

Step-2:

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

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

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)

applying formula

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

Using INDEX with MATCH to perform partial match string

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

Using INDEX with MATCH to perform partial match string

Press “Enter”. Our result is here.

final result

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

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo