**IF function** is one of the most common functions in Excel. Depending on circumstances different logical operations can be performed through this function. We can also perform a partial match using the IF function. Today we are going to show you six easy ways to use the IF function for the partial match in Excel. So, let’s start this article and explore these methods.

First things first, let’s get to know about the dataset which is the base of our examples. Here we have listed the **Addresses of Employees **of a company. Using this dataset, we will see how to use the **IF **function for partial matches.

Note that this is a basic table with a simple dataset to keep things straightforward. In a real-life scenario, you may encounter a much larger and more complex dataset.

As we know the **IF **function performs logic checks and returns the set true or false value. Here we will check whether a given state name is in the **Address **column. Since our agenda is to perform a partial match, we will set “New” as the search state.

Now write the formula in cell **F5**.

`=IF(D4="*"&$H$5&"*","Desired State","It's another State")`

Here we have used the** asterisk sign (*)** as wildcards. This denotes that any number of characters can be there. The “Desired State” is the ** value_if_true **argument and the “It’s another State” is the

**argument.**

*value_if_false*You can clearly see that the cell has “New” within it, but the formula returned “It’s another State”. The **IF **function can’t work with wildcards. We need another function along with the **IF **function to work with wildcards.

*Note: **We have used an asterisk (*) as a wildcard character in the first five methods to find the partial match. And the last one deals with a question mark (?).*

**Table of Contents**Expand

## 1. Combining IF and COUNTIF Functions for Partial Match in Excel

Using the combination of **IF **and **COUNTIF** functions is a smart way to find a partial match in Excel. Now, let’s follow the steps discussed in the following section.

__Steps:__

- Firstly, enter the following formula in cell
**F5**.

`=IF(COUNTIF(D5,"*"&$H$5&"*"),"Desired State","It's another State")`

Here, cell **D5 **indicates the selected **Address**, and cell **H5 **refers to the **Search State**.

**Formula Breakdown**

- Firstly, in the
**COUNTIF(D5,”*”&$H$5&”*”)**function,**D5**→ It is theargument.*range***“*”&$H$5&”*”**→ This represents theargument.*criteria***Output**→**1**.

- Now, in the
**IF**function,**COUNTIF(D5,”*”&$H$5&”*”)**→ This is theargument.*logical_test***“Desired State”**→ It indicates theargument.*[value_if_true]***“It’s another State”**→ This refers to theargument.*[value_if_false]***Output**→**Desired State**.

- Now, press
**ENTER**.

As a result, you will have the following output in cell **F5 **as we have found a partial match of the **Search State **in cell **D5**.

- Now, copy down the formula to the next cell using the
**Fill Handle**.

Subsequently, you will see that the formula returned “It’s another state”. Because there was no partial match for the **Search State **in cell **D6**.

Earlier, we checked “New” in a cell where the state name was** New York**, let’s check for a cell where the state name is **New Jersey**.

- Drag the
**Fill Handle**down to the next cell, and you will get the following output.

- Finally, use the
**AutoFill**option of Excel to obtain the remaining outputs. Here, we have found partial matches in cells**F5**,**F7**,**F8**, and**F10**respectively.

## 2. Joining Excel ISNUMBER and SEARCH Functions to Match Partially

Now, instead of the **COUNTIF **function, we will use the combination of the **ISNUMBER** and **SEARCH** functions. Let’s use the instructions outlined below to do this.

__Steps:__

- Firstly, use the following formula in cell
**F5**.

`=IF(ISNUMBER(SEARCH("*"&$H$5&"*",D5)),"Desired State","It's another State")`

**Formula Breakdown**

**SEARCH(“*”&$H$5&”*”,D5)**→ It returns the location of one text string inside another.**“*”&$H$5&”*”**→ This is theargument.*find_text***D5**→ This indicates theargument.*within_text***Output**→**1**.

- Now,
**ISNUMBER(SEARCH(“*”&$H$5&”*”,D5))**→ It becomes**ISNUMBER(1)**.**Output**→**TRUE**.

- In the
**IF**function,**ISNUMBER(SEARCH(“*”&$H$5&”*”,D5))**→ This is theargument.*logical_test***“Desired State”**→ It represents theargument.*[value_if_true]***“It’s another State”**→ It is theargument.*[value_if_false]***Output**→**Desired State**.

- Following that, press
**ENTER**.

Consequently, you will have the following output in cell **F5 **as the formula has found a partial match in the **Address **of cell **D5**.

- Lastly, by using the
**AutoFill**option of Excel, you can get the rest of the outputs as demonstrated in the image below.

## 3. Merging IF, ISNA, and MATCH Functions for Partial Match

Using the combination of **IF**, **ISNA**, and **MATCH** functions is another efficient way to find partial match in Excel. In the following section, we will learn the detailed steps to use these functions to find partial matches. So, let’s explore these steps.

__Steps:__

- Firstly, enter the formula given below in cell
**F5**.

`=IF(ISNA(MATCH("*New*",D5,0)),"Not Found!","Found")`

**Formula Breakdown**

**MATCH(“*New*”,D5,0)**→ It returns the relative position of a specified lookup value.- Here,
**“*New*”**→ It is theargument.*lookup_value* **D5**→ This indicates theargument.*lookup_array***0**→ It is theargument.*[match_type]***Output**→**1**.

- Here,
- Now,
**ISNA(MATCH(“*New*”,D5,0))**→ It becomes**ISNA(1)**.**Output**→**FALSE**.

- In the
**IF**function,**ISNA(MATCH(“*New*”,D5,0))**→ This is theargument.*logical_test***“Not Found!”**→ It indicates theargument.*[value_if_true]***“Found”**→ It refers to theargument.*[value_if_false]***Output**→**Found**.

- After that, press
**ENTER**.

Subsequently, you will have the following output in cell **F5 **as shown in the following picture.

- Finally, use the
**AutoFill**option of Excel to have the remaining outputs.

## 4. Applying OR Operation with IF Function to Match Partially

Let’s perform the **OR** operation within the **IF **partial match. We will use the **OR **function to compare the given values. Here, for example, we will check for the **State **or **Number Ext**. The **OR **function will return a **TRUE **value if any or both of the conditions are **TRUE**. Now, let’s follow the steps mentioned below to do this.

__Steps:__

- Firstly, apply the following formula in cell
**E5**.

`=IF(OR(ISNUMBER(SEARCH("*"&$F$5&"*",D5)),ISNUMBER(SEARCH($G$5,C5))),"Contact","")`

Here, cell **F5 **indicates the **Search State**, cell **G5 **refers to the **Number Ext.**, and cell **C5 **represents the first cell of the **Phone **column.

**Formula Breakdown**

- Here,
**two**logical arguments of**OR**function are,**ISNUMBER(SEARCH(“*”&$F$5&”*”,D5))**→ It returns**TRUE**.**ISNUMBER(SEARCH($G$5,C5))**→ This also returns**TRUE**.

- Now,
**OR(ISNUMBER(SEARCH(“*”&$F$5&”*”,D5)),ISNUMBER(SEARCH($G$5,C5)))**→ It becomes**OR(TRUE,TRUE)**.**Output**→**TRUE**.

- In the
**IF**function,**OR(ISNUMBER(SEARCH(“*”&$F$5&”*”,D5)),ISNUMBER(SEARCH($G$5,C5)))**→ It is theargument.*logical_test***“Contact”**→ This representsargument.*[value_if_true]***“”**→ This indicates theargument.*[value_if_false]***Output**→**Contact**.

- Then, hit
**ENTER**.

Subsequently, you will have the following output in cell **E5 **on your worksheet.

- Finally, use the
**AutoFill**feature of Excel to obtain the rest of the outputs as demonstrated in the image below.

## 5. Using AND Operation with Excel IF Function for Partial Match

Similar to the **OR **operation we can also perform the **AND** operation within the **IF **partial match. The **AND **function only returns **TRUE **when both values are **TRUE**. Now, let’s follow the steps outlined in the following section.

__Steps:__

- Firstly, use the formula given below in cell
**E5**.

`=IF(AND(ISNUMBER(SEARCH("*"&$F$5&"*",D5)),ISNUMBER(SEARCH($G$5,C5))),"Contact","")`

**Formula Breakdown**

- Here,
**two**logical arguments of the**AND**function are,**ISNUMBER(SEARCH(“*”&$F$5&”*”,D5))**→ This returns**TRUE**.**ISNUMBER(SEARCH($G$5,C5))**→ It also returns**TRUE**.

- Now,
**AND(ISNUMBER(SEARCH(“*”&$F$5&”*”,D5)),ISNUMBER(SEARCH($G$5,C5)))**→ It becomes**AND(TRUE,TRUE)**.**Output**→**TRUE**.

- In the
**IF**function,**AND(ISNUMBER(SEARCH(“*”&$F$5&”*”,D5)),ISNUMBER(SEARCH($G$5,C5))**→ It is theargument.*logical_test***“Contact”**→ This representsargument.*[value_if_true]***“”**→ This indicates theargument.*[value_if_false]***Output**→**Contact**.

- After that, press
**ENTER**.

As a result, the formula will return the following output in cell **E5 **as both the logical arguments of the **AND **function return a **TRUE **value.

- Finally, use the
**AutoFill**feature of Excel to have the rest of the outputs as shown in the following picture.

## 6. Finding Partial Match with Question Mark

Suppose you are searching for a partial match of a specific text. But you are unsure about the spelling of the text. For instance, let’s say, you are searching for the word **Cobleskill**. But you are confused about the spelling. It might be **Cobleskill **or **Cableskill**. If you don’t know the correct spelling, you won’t be able to find a match. To solve this issue, we will use a question mark. In the following section, we will discuss how to use it to find a partial match.

__Steps:__

- Firstly, apply the following formula in cell
**F5**.

`=IF(ISNA(MATCH("*C?bleskill*",D5,0)),"Not Found!","Found")`

**Formula Breakdown**

**MATCH(“*C?bleskill*”,D5,0)**→ It returns the relative position of a specified lookup value.- Here,
**“*C?bleskill*”**→ It is theargument.*lookup_value* **D5**→ This indicates theargument.*lookup_array***0**→ It is theargument.*[match_type]***Output**→**#N/A**.

- Here,
- Now,
**ISNA(MATCH(“*C?bleskill*”,D5,0))**→ It becomes**ISNA(#N/A)**.**Output**→**TRUE**.

- In the
**IF**function,**ISNA(MATCH(“*C?bleskill*”,D5,0))**→ This is theargument.*logical_test***“Not Found!”**→ It indicates theargument.*[value_if_true]***“Found”**→ It refers to theargument.*[value_if_false]***Output**→**Not Found!**.

- Then, press
**ENTER**.

Subsequently, you will have the following output as the formula didn’t find any partial match in cell **D5**.

- Lastly, by using Excel’s
**AutoFill**feature, you can get the remaining outputs.

You can see that we have found a partial match in cell **D8 **and the correct spelling is **Cobleskill**.

## How to Find Partial Match in Two Columns in Excel

In this section of the article, we will learn the detailed steps to find partial match in two columns in Excel. Let’s say, we have **Characters with Nicknames **as our dataset. Here, we will compare the **Character Name **with the **Nickname**. If the Nickname matches the Character Name, then in the **Comparison **column, the full **Character Name **will be returned. If it doesn’t match, the formula will return a blank.

At this stage, let’s follow the steps mentioned below.

__Steps:__

- Firstly, use the following formula in cell
**D5**.

`=IFERROR(VLOOKUP("*"&C5&"*",$B$5:$B$10,1,0),"")`

Here, cell **C5 **indicates the first cell of the **Nickname **column, and the range of cells** B5:B10** refers to the cells of the **Character Name **column.

**Formula Breakdown**

- Firstly, the
**VLOOKUP**function will return the value from the specified column of the given table, where the value matches the lookup value. - In the
**VLOOKUP(“*”&C5&”*”,$B$5:$B$10,1,0)**function,**“*”&C5&”*”**→ It is theargument.*lookup_value***$B$5:$B$10**→ This represents theargument.*table_array***1**→ It indicates theargument.*col_index_num***0**→ This is theargument.*[range_lookup]***Output**→**“Jon Snow”**.

- Next, the
**IFERROR**function will return a specific value against input for error criteria. - Here,
**IFERROR(VLOOKUP(“*”&C5&”*”,$B$5:$B$10,1,0),””)**→ It becomes**IFERROR(“Jon Snow”,””)**.- Here,
**“Jon Snow”**→ This is theargument.*value* **“”**→ This indicates theargument.*value_if_error***Output**→**Jon Snow**.

- Here,

- Following that, press
**ENTER**.

As a result, you will have the following output in cell **D5 **as the Nickname** Jon **is part of the **Character Name**.

- Finally, use the
**AutoFill**option of Excel to get the remaining outputs as demonstrated in the following image.

**Download Practice Workbook**

## Conclusion

So, these are the most common and effective methods you can use anytime while working with your Excel datasheet to use IF partial match in Excel. If you have any questions, suggestions, or feedback related to this article, you can comment below.

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