**Excel **is a powerful tool for data analysis and manipulation, and mastering partial matches in **Excel** can greatly enhance your ability to work with complex datasets. Partial matching allows you to find specific patterns or substrings within text strings, opening up a world of possibilities for data extraction, filtering, and analysis. This comprehensive guide will walk you through the procedures to check Partial Match in **Excel**.

**Download Practice Workbook**

You can download the practice workbook here and exercise.

**Table of Contents**Expand

**How to Find Partial Match in Excel**

In this section, we will discuss some methods to find partial matches in **Excel**. Let’s use some of the functions and features of **Excel**.

### 1. Using the ISNUMBER Function

In the first method, we will use **the ISNUMBER function** along with the **IF** and **OR** functions. The used formula will check the letters from **Match String 1 **and **Match String 2 **in column **Name**, if it finds, will return **Yas **otherwise **No**.

- Firstly, apply the following formula to a cell. Press
**Enter**. - Then, use the
**Fill Handle**to copy the formula into the following cells.

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

### 2. Applying the SEARCH Function

In the first method, we will use **the SEARCH function** along with the **IF **and the **ISNUMBER **functions to get our partial math detected. The used formula will check the letters from **Match String 1 **and **Match String 2 **in column **Name**, if it finds, will return **Yas **otherwise **Not found**.

- Firstly, apply the following formula to a cell. Press
**Enter**. - Then, use the
**Fill Handle**to copy the formula into the following cells.

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

### 3. Use of the VLOOKUP Function

In the first method, we will use **the VLOOKUP function**. If **VLOOKUP **finds the **Name**, it will deliver the rank.

- Firstly, apply the following formula to a cell. Press
**Enter**. - Also, use the
**Fill Handle**to copy the formula into the following cells.

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

### 4. Inserting XLOOKUP Function

In the first method, we will use **the XLOOKUP function**. It works the same as **VLOOKUP, **except it works horizontally.

- Firstly, apply the following formula to a cell. Press
**Enter**. - Then, use the
**Fill Handle**to copy the formula into the following cells.

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

### 5. Combining INDEX and MATCH Functions

In the first method, we will use the combination of the **INDEX** and **MATCH **functions. It works similarly to **the VLOOKUP function**.

- Firstly, apply the following formula to a cell. Press
**Enter**. - In addition, use the
**Fill Handle**to copy the formula into the following cells.

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

### 6. Combining Multiple Functions to Find Partial Match in Two Columns

In this method, we will use **the ISNUMBER function** along with the **IF,** **AND****, **and **SEARCH **functions. So, their combination can be used to get a partial match. If it finds the match then, it will show **Found**.

- Firstly, apply the following formula to a cell. Press
**Enter**. - Then, use the
**Fill Handle**to copy the formula into the following cells.

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

### 7. Applying Array Formula to Find Partial Match

In this method, we will use **the SEARCH function** along with the **IF **and **COUNT** functions. If the formula finds **A **in the name data of **Name **with the **ID **column then, it will give the output “**Found**”.

- Firstly, apply the following formula to a cell. Press
**Enter**. - Then, use the
**Fill Handle**to copy the formula into the following cells.

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

**How to Match Partial Numbers in Excel**

So far, we have checked for a partial match of string type. Of course, we can do a similar task for numbers as well. Let’s check out how to do that.

- Firstly, apply the following formula to a cell. Press
**Enter**. - In addition, use the
**Fill Handle**to copy the formula into the following cells.

`=IF(ISNUMBER(SEARCH("*"&E5,B5)),"Matched","Not Matched")`

**How to Match Partial Duplicates in Excel**

Also, why not check for the presence of partial duplicates in our dataset! We will simply use **the COUNTIF function** to count the number of duplicates in column **Formula 1**.

- Firstly, apply the following formula to a cell. Press
**Enter**. - Afterward, use the
**Fill Handle**to copy the formula into the following cells.

`=COUNTIF(C:C,C5)`

## How to Find the Partial Match Row Position in Excel

In addition, we will use **the MATCH function** along with the “**&**”** **logic to get the row number where two columns of data partially match.

- Firstly, apply the following formula to a cell. Press
**Enter**. - Again, copy the formula into the following cells using the
**Fill Handle**.

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

**Things to Remember**

- Please keep in mind, if the
**VLOOKUP**function can’t find the lookup value in the lookup table, it’ll return an error. - Also, if the
**XLOOKUP**function can’t find the lookup value in the lookup table, it’ll return an error. - Sometimes, we used absolute cell references with the “
**$**” sign to copy them without formula adaptation. - Also, remember
**Excel**is not case-sensitive by default, so the methods we have shown count all the partial matches despite their case criteria.

**Conclusion**

Partial match in **Excel **is a valuable skill that empowers users to efficiently search, extract, and analyze data based on specific patterns or substrings. By utilizing functions like **VLOOKUP**, **INDEX/MATCH**,** COUNTIF**, and **SUMIF**, users can perform powerful partial matching operations. Additionally, we have shown the ways to find partial duplicates, partial match positions, etc. Kindly, leave comments if you have any.

**Frequently Asked Questions**

**1. What is a partial match in Excel?**

**Ans:** In** Excel**, a partial match refers to finding a substring or partial text within a larger text string or number. Also, It allows you to search for a specific pattern or set of characters within a cell or range of cells.

**2. Can I perform a partial match with multiple criteria in Excel?**

**Ans:** Yes, you can perform a partial match with multiple criteria in **Excel** by using array formulas or advanced functions like **ISNUMBER,** **SEARCH, **etc. Also, these functions allow you to specify multiple criteria and perform partial matching for each criterion.

**3. Can I perform a partial match with numbers or numeric values in Excel?**

**Ans:** Yes, you can perform a partial match with numbers or numeric values in **Excel**. The same partial matching functions like** VLOOKUP**, **INDEX/MATCH**, **COUNTIF**, and **SUMIF** can be used with numbers. **Excel **will consider numeric values as text when performing partial matching operations. However, it’s important to ensure that the number formatting and data types are consistent to avoid unexpected results.

## Partial Match Excel: Knowledge Hub

**<< Go Back to Formula List | Learn Excel**