## Download the Practice Workbook

## 8 Methods to Perform a Partial Match for a String in Excel

### Method 1 – Using IF and OR Statement to Perform a Partial Match for String

The **IF** function does not support **wildcard** characters. Combining** IF** with other functions can be used for a partial match.

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

**Steps:**

- In cell
**E5**, apply the following formula:

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

- Insert the values into the formula:

`=IF(OR(ISNUMBER(SEARCH(C5,B5)),ISNUMBER(SEARCH(D5,B5))),"YES","NO")`

**Formula Breakdown**

- The text is
**C5 (A), D5 (L)**. The SEARCH will try to find the strings inside the cell. - The cell is
**B5 (Jonathan)**. **Value_if_true**is**“YES”**.**Value_if_false**is**“NO”**.

- Press
**Enter**.

- Drag the
**Fill Handle**icon down to**AutoFill**the corresponding formula into the rest of the cells.

- Here’s the result.

**Read More: ****How to Find Partial Match in Two Columns in Excel (4 Methods)**

### Method 2 – Use IF, ISNUMBER, and SEARCH Functions for a Partial Match

Consider a data set containing the columns **Name**,** Match String**, and** Status**. We need to identify the names that contain the** string** from the column** Match String**.

- Apply the formula with the
**IF, ISNUMBER**, and**SEARCH**functions in the**“Status”**column in cell**D5:**

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

- After changing for the values in the sample, the formula is:

`=IF(ISNUMBER(SEARCH(C5,B5)),"YES","NOT FOUND")`

- Press
**Enter**.

**Formula Breakdown**

- The text is
**C5 (A)**. The formula will check whether**C5**is inside the cell. - The cell is
**B5 (Jonathan)**. **Value_if_true**is**“YES”**.**Value_if_false**is**“NOT FOUND”**.

- Apply this formula for all the cells in the column to find out all the results that contain a
**partial match string**.

### Method 3 – Using the VLOOKUP Function to Perform a Partial Match

Let’s consider a table where the names of some candidates and their ranks are given.

- Copy the column headers and paste them elsewhere in the worksheets.

- Insert the strings into the Name column in the new table. These will be the search values.
- Apply the
**VLOOKUP**function in the**F5**cell:

`=VLOOKUP($E$5&"*",$B$5:$C$10,2,FALSE)`

**Formula Breakdown**

**Lookup_value**is**$E$5&”*”**. We used the**Asterisk (*)**as a wildcard that matches**zero**or more characters.**Table_array**is**$B$5:$C$10**.**Col_index_num**is**2**.**[range_lookup]**is**FALSE**as we want the exact match**.**

- Press
**Enter**.

- Drag the formula down to the other cells in the column.

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

### Method 4 – Incorporating the XLOOKUP Function to Perform a Partial Match

In the** first** table, the **partial match strings **are given with rank. We need to identify the names in the **second** table that contain the **partial match** strings and then return the rank associated with those names.

- In cell
**F5**, apply the formula.

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

- After inserting the values, the formula becomes:

`=XLOOKUP(TRUE,ISNUMBER(SEARCH($B$5:$B$10,E5)),$C$5:$C$10)`

- Hit Enter.

**Formula Breakdown**

**lookup_value**is**“TRUE”**.- The text is
**$B$5:$B$10**. - The cell is
**E5 (Henry Jonathan)**. The formula in the first cell will return the rank for**Henry Jonathan.** **return_array**is**$C$5:$C$10**.

- AutoFill to the other cells.

**Read More: ****Lookup Partial Text Match in Excel (5 Methods)**

### Method 5 – Using the INDEX-MATCH Formula to Perform Partial Matching

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 from the** first** table that contains the **partial match **strings.

- In cell
**F5**, apply the formula:

`=INDEX($B$5:$B$10,MATCH(E5&"*",$B$5:$B$10,0))`

- Hit Enter.

We got the Name** “Robben”** which contains the **partial match string (Rob).**

**Formula Breakdown**

- The array is
**$B$5:$B$10**. **lookup_value**is**E5&”*”**. We used the**Asterisk (*)**as a**wildcard**that matches**zero**or more characters.**lookup_array**is**$B$5:$B$10**.**[match_type]**is**EXACT (0).**

The** Asterisk(*)** can be used on both sides of the cell if you expect characters on both sides of your **partial match string**.

- Use the following formula in the
**F6**cell.

`=INDEX($B$5:$B$10,MATCH("*"&E6&"*",$B$5:$B$10,0))`

- Press
**Enter**to get the result.

### Method 6 – Combined Functions to Perform a Partial Match with Two Columns

We have** two** criteria that we need to match by.

**Steps:**

- Select a new cell
**C5**where you want to keep the status. - Use the formula below in the
**C5**cell.

`=IF(AND(ISNUMBER(SEARCH($E$6, B5)), ISNUMBER(SEARCH($F$6, B5))), "Found", "")`

- Hit Enter to get the result.

**Formula Breakdown**

**SEARCH($F$6, B5)**will search if there are any strings**Ad**in the**B5**cell.**Output: #VALUE!.**

- The
**ISNUMBER**function will check whether the above output is a number or not.**Output: FALSE.**

**ISNUMBER(SEARCH($E$6, B5))**will do the same for the second partial match string.**Output: FALSE.**

- The
**AND**function will check whether both values are**TRUE.****Output: FALSE.**

- The
**IF**function will return “**Found”**if**AND**returns**TRUE.**Otherwise, it will return a**void cell.****Output:**The output is**blank/empty**as there is no match for the string value of the**B5**cell.

- Drag the
**Fill Handle**icon to**AutoFill**the formula in the rest of the cells.

**Read More: ****How to Use IF Function to Find Partial Match in Excel (6 Ways)**

### Method 7 – Applying an Array Formula to Find a Partial Match with Two Columns

We have** two** criteria to use for **partial matches**.

**Steps:**

- Select cell
**C5**. - Insert the following formula:

`=IF(COUNT(SEARCH({"A","12"}, B5))=2, "Found", "")`

- Press
**Enter**to get the result.

**Formula Breakdown**

**SEARCH({“A”,”12″}, B5)**will search for the string**A**and the number**12**in the**B5**cell.**Output: {#VALUE!,7}.**

- The
**COUNT**function will count the number of “hits” in the cell.**Output: 1.**

- The
**IF**function will return “**Found”**if the**COUNT**function returns**2.**Otherwise, it will return a**void cell.****Output:**The output is**blank/empty**as there is only one match, but we need two.

- Drag the
**Fill Handle**icon to**AutoFill**the corresponding data in the rest of the cells.

### How to Get the Position of a Partial Match in Excel

We have to extract the **partial string **from the** “Name with Rank” **column and locate where it is in the searched list.

**Steps:**

- Use the formula given below in the
**D9**cell.

`=MATCH("*"&D6&"*", B5:B10, 0)`

- Press
**Enter**to get the result.

**Formula Breakdown**

**lookup_value**is**“*”&D6&”*”**.**lookup_array**is**B5:B10**.**[match_type]**is**EXACT (0).**

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

## Things to Remember

✅ The** XLOOKUP** function is only available in **Microsoft 365 version**. So, 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. Moreover, this function never fetches 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.

## Practice Section

We’ve included a practice section you can use to test these methods.

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