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

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.

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

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.

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

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

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

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

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

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

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

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.

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

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

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

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

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.

### 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","")`

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

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.

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

- Lookup Partial Text Match in Excel (5 Methods)
- How to Use INDEX and Match for Partial Match (2 Ways)
- Excel Partial Match Two Columns (4 Simple Approaches)
- How to Use VLOOKUP for Partial Match in Excel (4 Ways)
- How to Perform Partial Match String in Excel (5 Methods)
- VLOOKUP Partial Text from a Single Cell in Excel