How to Use IF Function to Find Partial Match in Excel (6 Ways)

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.

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 value_if_false argument.

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 (?).

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.

Here, cell D5 indicates the selected Address, and cell H5 refers to the Search 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.

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

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

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.

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

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

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

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.

• 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