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.

**Table of Contents**hide

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

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

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

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

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.

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

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

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.

**=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.**

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,

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

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.