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.


Download Practice Workbook


6 Diverse Ways to Use IF Function to Find Partial Match in Excel

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.

if partial match excel

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. And 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. Using Combination of IF and COUNTIF Functions

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 the range argument.
    • “*”&$H$5&”*” → This represents the criteria argument.
    • Output 1.
  • Now, in the IF function,
    • COUNTIF(D5,”*”&$H$5&”*”) → This is the logical_test argument.
    • “Desired State” → It indicates the [value_if_true] argument.
    • “It’s another State” → This refers to the [value_if_false] argument.
    • Output Desired State.
  • Now, press ENTER.

Using Combination of IF and COUNTIF Functions to use IF partial match in Excel

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

Using Fill Handle to use IF partial match in Excel

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.

Final output of method 1 to use IF partial match in Excel

Read More: COUNTIF Partial Match (Couple of Easy Approaches)


2. Utilizing Combination of ISNUMBER and SEARCH Functions

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 the find_text argument.
    • D5 → This indicates the within_text argument.
    • 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 the logical_test argument.
    • “Desired State” → It represents the [value_if_true] argument.
    • “It’s another State” → It is the [value_if_false] argument.
    • Output Desired State.
  • Following that, press ENTER.

Utilizing Combination of ISNUMBER and SEARCH Functions to use IF partial match in Excel

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.

Final output of method 2 to use IF partial match in Excel


3. Employing IF, ISNA, and MATCH Functions

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 the lookup_value argument.
    • D5 → This indicates the lookup_array argument.
    • 0 → It is the [match_type] argument.
    • Output 1.
  • Now, ISNA(MATCH(“*New*”,D5,0)) →  It becomes ISNA(1).
    • Output FALSE.
  • In the IF function,
    • ISNA(MATCH(“*New*”,D5,0)) → This is the logical_test argument.
    • “Not Found!” → It indicates the [value_if_true] argument.
    • “Found” → It refers to the [value_if_false] argument.
    • Output Found.
  • After that, press ENTER.

Employing IF, ISNA, and MATCH Functions to use IF partial match in Excel

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.

Final output of method 3 to use IF partial match in Excel


4. Applying OR Operation in IF Partial Match

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.

Applying OR Operation in IF Partial Match to use IF partial match in Excel

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 the logical_test argument.
    • “Contact” → This represents [value_if_true] argument.
    • “” → This indicates the [value_if_false] argument.
    • 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.

Final output of method 4 to use IF partial match in Excel


5. Using AND Operation in IF 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 the logical_test argument.
    • “Contact” → This represents [value_if_true] argument.
    • “” → This indicates the [value_if_false] argument.
    • Output Contact.
  • After that, press ENTER.

Using AND Operation in IF Partial Match to use IF partial match in Excel

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

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

Final output of method 5 to use IF partial match in Excel


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 the lookup_value argument.
    • D5 → This indicates the lookup_array argument.
    • 0 → It is the [match_type] argument.
    • Output #N/A.
  • 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 the logical_test argument.
    • “Not Found!” → It indicates the [value_if_true] argument.
    • “Found” → It refers to the [value_if_false] argument.
    • Output Not Found!.
  • Then, press ENTER.

Finding Partial Match with Question Mark to use IF partial match in Excel

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.

Final output of method 6 to use IF partial match in Excel


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.

How to Find Partial Match in Two Columns in Excel

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 the lookup_value argument.
    • $B$5:$B$10 → This represents the table_array argument.
    • 1 → It indicates the col_index_num argument.
    • 0 → This is the [range_lookup] argument.
    • 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 the value argument.
    • “” →  This indicates the value_if_error argument.
    • Output Jon Snow.
  • 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.

Final output of method 7 to Find Partial Match in Two Columns in Excel


Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it yourself.

Practice section to use IF partial match in Excel


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. You can also have a look at our other useful articles on Excel functions and formulas on our website, ExcelDemy.


Further Readings

Shakil Ahmed

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo