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.

**Table of Contents**hide

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

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

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

**Read More:** **How to Use INDEX and Match for Partial Match (2 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.