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