How to Use IF Partial Match in Excel (4 Basic Operations)

IF 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 IF. Today we are going to show you how to use IF for the partial match.

First things first, let’s get to know about the workbook which is the base of our examples.

Dataset - IF Partial Match Excel

Here we have listed the contact information of a few random people. Using this dataset we will see how to use IF 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 complex dataset.

Practice Workbook

You are welcome to download the practice workbook from the link below.

IF in Partial Match

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.

Criteria - IF Partial Match Excel

Since our agenda is to perform a partial match we will set “New” as the search state.

Criteria value - IF Partial Match Excel

Now write the formula using IF. Before that, for a reminder, you can check this IF article.

=IF(D4="*"&$H$4&"*","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.

IF partial Formula - IF Partial Match Excel

And the “Desired State” is the if_true_value and the “It’s another State” is the if_false_value.

IF Formula result

You can see the cell has “New” within it, but the formula returned the if_false_value. The IF function can’t work with the wildcards. We need another function along with IF to work with wildcards.

1. Combination of IF – COUNTIF

A function we can use alongside IF is the COUNTIF function. This function counts cells in a range that meets a single condition. To know more, visit this article: COUNTIF.

The formula will be the following one

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

Here we have checked the logic using COUNTIF. Using COUNTIF it checks whether the count value is 1 or not.

IF-COUNTIF Formula - IF Partial Match Excel

If COUNTIF returns 1 (since only one cell is being checked) then the if_true_value will be returned. For 0 from the COUNTIF portion, the formula will return if_false_value.

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

Here our checking cell has New York in it, so the formula will return if_true_value.

IF-COUNTIF formula result

When the checking cell doesn’t have a “New” value within it then will return the value for false.

IF-COUNTIF result 2

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.

IF - COUNTIF result 3

As long as the cell contains the value “New” in it the formula will return the if_true_value (Desired State).

Write the formula for the rest of the cells using the AutoFill.

IF-COUNTIF autofill

2. Combination of ISNUMBER – SEARCH

Instead of the COUNTIF function, we can use the combination of ISNUMBER-SEARCH.

ISNUMBER returns TRUE when a cell contains a number, and FALSE if not.

SEARCH returns the location of one text string inside another. To know about these functions more, visit these articles:  ISNUMBER, and SEARCH.

We can use FIND in place of SEARCH to make it case-sensitive.

Now the formula will be the following one

=IF(ISNUMBER(SEARCH("*"&$H$4&"*",D4)),"Desired State","It's another State")

Within the SEARCH function, we looked for the criteria value inside the range. As you know this function returns the starting position of the searh_text if find, error otherwise.

ISNUMBER - SEARCH formula - IF Partial Match Excel

The ISNUMBER function checks whether the SEARCH function’s result is a number or not. It returns a boolean value.

When the ISNUMBER returns TRUE then the IF function will trigger the if_true_value, otherwise the if_false_value.

ISNUMBER - SEARCH formula result

Here for the first cell, the ISNUMBER-SEARCH returned TRUE and the final output became “Desired State”.

Write the formula for the rest of the cells.

ISNUMBER - SEARCH result AutoFill

3. OR Operation in IF Partial Match

Let’s perform the OR operation within the IF partial match. Here, for example, we will check for the state or contact number.

OR operation data criteria - IF Partial Match Excel

We will use the OR function to compare the given values. To know about the function, visit the article: OR.

Now the formula will be the following one

=IF(OR(ISNUMBER(SEARCH("*"&$H$4&"*",D4)),ISNUMBER(SEARCH($I$4,C4))),"Contact","")

Hope you have understood the operation of ISNUMBER-SEARCH. The two ISNUMBER – SEARCH portions check the state and number. And set it within OR.

OR operation - IF Partial Match Excel

And the OR function counts the values in such a way that when both or any of the values are TRUE then the formula will return if_true_value.

Here for simplicity, we have only set the if_ true_value but no if_false_value.

OR operation result

Here the number extension has 60 and the state name has “New”.  

OR operation result false

This time both the ISNUMBER-SEARCH portions returned FALSE and the IF function returned if_false_value (empty).

Write the formula for the rest of the rows and see the results.

OR formula results

4. AND Operation in IF Partial Match

Similar to the OR operation we can perform the AND operation within the IF partial match. To know about the function, visit the article: AND.

The AND function only returns TRUE when both the values are true. The formula will be the one stated below

=IF(AND(ISNUMBER(SEARCH("*"&$H$4&"*",D4)),ISNUMBER(SEARCH($I$4,C4))),"Contact","")

AND operation formula - IF Partial Match Excel

As we know the formula will return if_true_value only when AND will return TRUE and it returns TRUE for both the values being TRUE.

AND operation formula result

Here these cells have both the values we are searching for and that’s why we have found the if_true_value.

Write the formula for the rest of the values and you will find the true value only for the rows which have met both the conditions.

AND operation results

Conclusion

That’s all for today. We have listed several operations of IF in partial matching. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know any other methods that we might have missed here.


Further Readings

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