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
    What happens if you partially match?
    That way, if you do not match into your advanced specialty, you can still match into a PGY-1 position and move forward with your medical training. This is called a partial match. more
    Does Lowes match Costco?
    Yes, Lowe's will happily match Costco pricing. more
    Does Citi match 401k?
    Citi matches your contributions to help you reach your retirement goals. In general, you are eligible for matching contributions after you have worked at Citi for one year. If you meet certain requirements, Citi also provides a fixed contribution of up to 2% of your eligible pay. more
    Does Costco match prices?
    Please rest assured that your satisfaction is our top priority—that's why we're always working to bring you brand-name items at the lowest possible prices. However, because we already offer competitive prices at the point of purchase, we do not price match with other retailers. more
    Is it possible to not match on Match Day?
    Not matching once is pretty common. If you ask around, you will find that many doctors had to apply to medical school two or even three times. Same thing with the match. I know very good doctors who had to scramble or apply twice after not matching or only partially matching. more
    Does LYFT match 401k?
    Lyft 401K Plan Good plan through Fidelity, but no matching is a big long term hit to people's retirement goals. more
    What is match types how many match types are there on Amazon?
    There are 3 Amazon PPC match types and using all three correctly will allow you to achieve a high ad rank. The match type you select will allow you to tell the platform how “tightly” to bid on a keyword/ad combination to match a customer search query. The 3 main match types are Broad, Phrase, and Exact. more
    Does Costco match 401K?
    The Company matches 50% of the employee's contribution, up to a maximum employer matching contribution of $500 per year. more
    Does JPM match 401k?
    You're eligible for match if you have completed one year of service and have Total Annual Cash Compensation of less than $250,000 (determined as of August 1 to take effect the following January 1 and remain unchanged throughout the year). more
    What is partial withdrawal?
    Partial withdrawal is among the several advantages offered by ULIPs that allows you to withdraw money for your needs from the accumulated fund value. ULIPs invest in the market through various funds of varying degree of risks for long-term wealth creation. Read on to know more about this unique facility. more
    Does blue match black?
    BLUE AND BLACK can be more problematic. Light and medium shades of blue generally work well with black (as with a light blue shirt and a black blazer or suit). Without a jacket, a light blue shirt and black trousers may make you look like an officer but, who knows, that may be a look you want. more

    Source: www.exceldemy.com

    You may be interested in...

    How much money can I make from TikTok live?

    What is the biggest challenge of older adults?

    What is small talk Mcq?

    Is TV better than cinema?

    What is eating my cucumber leaves at night?

    How long does it take for diabetic ketoacidosis to develop?

    How do the British pronounce button?

    What do period blood clots mean?

    Why do Shih Tzus like belly rubs?

    What is the smallest unit of Ethereum?

    Who is No 1 city in India?

    How do I train my dog to ignore other dogs off the lead?

    Is there vitamin K2 in yogurt?

    Why is green Satoshi token?

    What is the most bought dog in Australia?

    About Privacy Contact
    ©2022 REPOKIT