Excel IF statement for partial text match (wildcard)

Trying to build an IF statement with wildcard text, but it fails every time? The problem is not in your formula but in the function itself - Excel IF does not support wildcard characters. However, there is a way to get it to work for partial text match, and this tutorial will teach you how.

Whenever you want to perform partial or fuzzy matching in Excel, the most obvious solution is to use wildcards. But what if a specific function that you need to use does not support wildcards characters? Sadly, Excel IF is one of such functions. This is especially disappointing considering that other "conditional" functions such as COUNTIF, SUMIF, and AVERAGEIFS work with wildcards perfectly well.

Luckily, it is not the obstacle that can stop a creative Excel user :) By combining IF with other functions, you can force it to evaluate a partial match and get a nice alternative to an Excel IF wildcard formula.

Why Excel IF function with wildcard not working

In the sample table below, supposing you want to check whether the IDs in the first column contain the letter "A". If found - display "Yes" in column B, if not - display "No".

It seems like including wildcard text in the logical test would be an easy solution:

=IF(A2="*a*","Yes", "No")

But regrettably it does not work. The formula returns "No" for all the cells, even those that contain "A":
Excel IF function with wildcard not working

Why does a wildcard IF statement fail? From all appearances, Excel doesn't recognize wildcards used with an equal sign or other logical operators. Taking a closer look at the list of functions supporting wildcards, you will notice that their syntax assumes a wildcard text to appear directly in an argument like this:

=COUNTIF(A2:A10, "*a*")

Excel IF contains partial text

Now that you know the reason why a wildcard IF formula fails, let's try to figure out how to get it to work. For this, we'll simply embed a function that accepts wildcards in the logical test of IF, namely the COUNTIF function:

IF(COUNTIF(cell, "*text*"), value_if_true, value_if_false)

With this approach, IF has no problem with understanding wildcards and flawlessly identifies the cells that contain either "A" or "a" (since COUNTIF is not case-sensitive):

=IF(COUNTIF(A2, "*a*"),"Yes", "No")

This formula goes to B2, or any other cell in row 2, and then you can drag it down to as many cells as needed:
Excel IF statement with wildcard text

This solution can also be used to locate strings of a specific pattern. Assuming only the IDs consisting of 2 groups of 2 characters separated with a hyphen are valid, you can use the "??-??" wildcard string to identify them:

=IF(COUNTIF(A2, "??-??"), "Valid", "")
IF wildcard formula to identify strings of a specific pattern

How this formula works:

For the logical test of IF, we use the COUNTIF function that counts the number of cells matching the specified wildcard string. Since the criteria range is a single cell (A2), the result is always 1 (match is found) or 0 (match is not found). Given that 1 equates to TRUE and 0 to FALSE, the formula returns "Valid" (value_if_true) when the count is 1 and an empty string (value_if_false) when the count is 0.

IF ISNUMBER SEARCH formula for partial matches

Another way to force Excel IF to work for partial text match is to include either the FIND or SEARCH function in the logical test. The difference is that FIND is case-sensitive while SEARCH is not.

So, depending on whether you want to treat lowercase and uppercase as the same or different characters, one of these formulas will work a treat:

Case-insensitive formula for partial match:

IF(ISNUMBER(SEARCH("text", cell)), value_if_true, value_if_false)

Case-sensitive formula for partial match:

IF(ISNUMBER(FIND("text", cell)), value_if_true, value_if_false)

As both functions are designed to perform a "cell contains" type of match, wildcards aren't really needed in this case.

For example, to detect IDs containing "A" or "a", the formula is:

=IF(ISNUMBER(SEARCH("A", A2)), "Yes", "No")

To only search for a capital "A" and ignore "a", the formula is:

=IF(ISNUMBER(FIND("A", A2)), "Yes", "No")

In B6 in the screenshot below, you can observe the difference in the result:
Excel IF formula for partial matches

How this formula works:

At the heart of the formula, there is a combination of ISNUMBER and SEARCH (or FIND):

ISNUMBER(SEARCH("A", A2))

The SEARCH function looks for the specified text ("A" in this example) and returns its position within a string in A2. If the text is not found, a #VALUE error is returned. As both SEARCH and FIND are designed to perform a "cell contains" type of match, wildcards aren't really needed in this case.

The ISNUMBER function converts a number to TRUE and any other value including error to FALSE. The logical value goes directly to the logical test of IF. In our case, A2 contains "A", so ISNUMBER returns TRUE:

IF(TRUE, "Yes", "No")

As the result, IF returns the value set for the value_if_true argument, which is "Yes".

Excel IF OR statement with wildcards

Need to identify cells that contain one of wildcard text strings? In this case, you can combine the classic IF OR statement with the COUNTIF or ISNUMBER SEARCH formula discussed above.

For example, to search for "aa" OR "bb" in A2 ignoring the letter case and return "Yes" if either is found, use one of these formulas:

=IF(OR(ISNUMBER(SEARCH("aa", A2)), ISNUMBER(SEARCH("bb", A2))), "Yes", "")

or

=IF(OR(COUNTIF(A2, "*aa*"), COUNTIF(A2, "*bb*")), "Yes", "")

Adding up two COUNTIF functions will also work. In this case, the plus sign works like the OR operator:

=IF(COUNTIF(A3, "*aa*") + COUNTIF(A3, "*bb*"), "Yes", "")

Instead of hardcoding wildcard strings in the formula, you can input them in separate cells, say D2 and F2, as shown in the screenshot below. Please notice that these cell references are locked with the $ sign so that the formula copies correctly to the below cells:

=IF(OR(COUNTIF(A2, "*"&$D$2&"*"), COUNTIF(A2, "*"&$F$2&"*")), "Yes", "")
Excel IF OR statement with wildcards

The above formulas work well for 2 partial matches, but if you are searching for 3 or more, they would become too lengthy. In this case, it stands to reason to approach the task differently:

Supply multiple substrings to the SEARCH function in an array constant, count the returned numbers, and check if the result is greater than zero (which would mean that at least one of the substrings if found):

=IF(COUNT(SEARCH({"aa","bb"}, A2))>0, "Yes", "")

This way, you will get exactly the same result with a more compact formula:
A more compact alternative to Excel IF OR wildcard formula

Excel IF AND formula with wildcards

When you want to check if a cell contains two or more different substrings, the easiest way is to use the COUNTIFS function with wildcards for the logical test.

Supposing you want to locate cells in column A that contain both "b" AND "2". To have it done, use "*b*" and "*2*" for COUNTIFS's criteria and A2 for the criteria range:

=IF(COUNTIFS(A2, "*b*", A2, "*2*"), "Yes", "")

Another way is to use the IF AND formula together with ISNUMBER SEARCH:

=IF(AND(ISNUMBER(SEARCH("b", A2)), ISNUMBER(SEARCH("2", A2))), "Yes", "")

Though we do not include any wildcard characters in this formula, it does work like searching for two wildcard strings ("*b*" and "*2*") in the same cell.

Of course, nothing prevents you from entering the search values in predefined cells, D2 and F2 in our case, and supplying the cell references to the formula:

=IF(AND(ISNUMBER(SEARCH($D$2, A2)), ISNUMBER(SEARCH($F$2, A2))), "Yes", "")
Excel IF AND formula with wildcards

If you prefer using more compact formulas wherever possible, then you may better like the array constant approach. The IF COUNT SEARCH formula is very much like in the previous example, but because this time both substrings must appear in A2, we check if the count is equal to 2:

=IF(COUNT(SEARCH({"b","2"}, A2))=2, "Yes", "")
An alternative to the IF AND formula with wildcards

These are the main methods of using wildcard in IF statement in Excel. If you know any other solutions, other users will certainly appreciate if you share your experience in comments. I thank you for reading and hope to see you on our blog next week!

Practice workbook for download

Excel IF wildcard formula examples (.xlsx file)

29 comments

  1. Hi, I wonder if this is possible. I have 5 questions that have 4 answers, each answer has a unique value; 1010, 1001, 110 and 101. the 5 question scores are then being totalled, I need to extract and identify scores in each column of 3 or more. I'm trying to use it to identify which of four quadrants someone falls in. Is it possible to do this with wildcards/ excel formula? Thanks in advance

    1. Hi! You can extract each character into a separate cell using the MID function and convert it to a number. You can then perform the mathematical calculations you need with these numbers.

      =--MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)

      Also, to calculate the sum of scores, you can combine all values into one text string using the CONCAT function. Then, as in the previous formula, divide the text into individual digits and calculate their sum using the SUMPRODUCT formula.

      =SUMPRODUCT(--MID(CONCAT(A1:A4), ROW(INDIRECT("A1:A"&LEN(CONCAT(A1:A4)))),1))

      1. Thank you, the MID formula does work but I must admit I am not understanding the formula well enough to know how to change it to export the individual values in to columns, any tips please?

  2. Hello!

    I wonder if what I'm trying to do is possible. And if it is, how would I go about it.

    I have 2 Excel sheets:
    Sheet #1 contains a list of names (example: Joe, Abby, George)
    Sheet #2, on column A has names with age (example: A1: Joe 45, A2: Milton 60, A3: Abby 36, A4: Abby 50). I want column B on Sheet #2 to mark "yes" if the column A contains a name from Sheet #1. Example: B1 would be "yes" since A1 (Joe 45) has "Joe", which is on Sheet #1. B2 would be "no" because the name "Milton" is not on Sheet #1. B3 & B4 would be "yes" because it contains "Abby" which appears on Sheet #1.

    The problem I'm running into is Sheet #1 doesn't contain the age, so it won't be exact match.

    I hope that make sense. Any help is appreciated!

    1. Hello Pete!
      If you want to find a partial match for a cell value in a column, you can use the following instructions:
      Excel wildcard VLOOKUP formula:

      =IF(ISERROR(VLOOKUP("*"&A1&"*",Sheet2!A1:A30,1,0)),"No","Yes")

      Count cells that contain certain text (partial match):

      =IF(COUNTIF(Sheet2!A1:A30,"*"&A1&"*") > 0,"Yes","No")

      XLOOKUP with wildcards:

      =IF(ISERROR(XLOOKUP("*"&A1&"*",Sheet2!A1:A30,A1:A30,,2)),"No","Yes")

      How to match partial text in Excel with wildcards:

      =IF(ISERROR(XMATCH("*"&A1&"*",Sheet2!A1:A30,2)),"No","Yes")

      ISERROR function can be used to help you to catch errors and to provide an alternative if an error is found.

  3. Hello,

    I have an excel sheet that has 6000 cities and need to put the state in a separate column. The state is in the original column, but I would like to do some macro research rather than gathering data on individual cities. How do I get the state to auto populate? For example: If my first 3 cells contain Miami, FL, Atlanta, GA and Las Angeles, CA...I would like a text return of FL, GA, CA respectively. What is the best function to have multiple rules in one text return fucntion?

    1. Hi! You can extract the last 2 characters after a space or the last word from the text using these formulas:

      =RIGHT(A1,2)
      =TEXTAFTER(A1," ")
      =TRIM(RIGHT(SUBSTITUTE(A1, " ", REPT(" ", LEN(A1))), LEN(A1)))

      Look for the more examples formulas here: Get last word from string.

  4. Hi Alexander

    I'm hoping you'll be able to help :)

    I am looking for a partial vlookup. And example would be:

    In tab 1 it might say; Michelle is bad at excel
    In tab 2 it will say: She is not good at excel

    I need to do a partial search - and then bring back a row in the 2nd tab (to the first tab) if "excel" (as an example) is in both tabs - Does that make sense? The issue is - "excel" won't be the same in every line (I have about 600 lines) which has somewhat matching info, but slightly different!

    Thanks!
    Michelle

  5. Hi,

    I'm looking for a way to see if a field contains a wildcard match from a list from a different sheet.

    So: if field A2 is 'www.website.com/full-url.html' I'd hope to see 'YES' in B2, and for A3 -which is 'www.website-no2.com/url-string.php' - it would return 'NO' in B3, with the other sheet containing the following list:

    example.com
    websites.com
    website.com
    another-example.com

    I'm aware that the other way around is easier, but I have to match multiple sheets with the same list...

    Thanks a lot for your help!

    1. Hello!
      To determine partial matches between text strings, use the SEARCH and ISNUMBER functions.

      =IF(SUM(--ISNUMBER(SEARCH($D$1:$D$4,A2))),"Yes","No")

      Hope this is what you need.

  6. Hi,

    I want max partial match formula in excel. Few example given below.
    MR. S CHANDRAN - CHANDRAN S = match
    MR SAI RATNAKAR KODE - KODE SAI RATNAKAR = match
    MR UMESH UMESH ARUN KALE - UMESH ARUN KALE = match

  7. Hi,
    I'm struggling with matching two worksheets with names ( last name, first name). One worksheet includes names with middle name initials and the other doesn't( it's a large file).
    I'm using the formula:
    =ISNUMBER(MATCH(A1,Datasource'!$A:$A,0))
    A1 = names from worksheet 1 ( names without middle initials)
    Datasource = worksheet 2 ( names with middle initials)
    I'm getting a true/false result. Some results show "FALSE" even though they should be "TRUE" only because names in sheet 1 don't include middle names.
    How can match them partially so worksheet 1 matches worksheet 2?
    Please help! Thanks.
    Joy

    1. Hello!
      To determine partial text matches, you can use the SEARCH function.
      If I got you right, the formula below will help you with your task:

      =ISNUMBER(MATCH(TRUE,ISNUMBER(SEARCH(A1,Datasource'!A1:A100)),0))

      I hope my advice will help you solve your task.

      1. Thank you for your prompt reply.
        I tried the formula you provided, and it worked!
        Thank you! I greatly appreciate your help!

        Joy

  8. Tried many examples of the =IF(ISNUMBER(SEARCH("A", A2)), "Yes", "No") function, but none of them work.

  9. =IF(COUNTIF(AC8, "*Tue*"),"40", ""+COUNTIF(AC8, "*Wed*"),"32.5", ""+COUNTIF(AC8, "*Fri*"),"40", ""+COUNTIF(AC8, "*Sat*"),"48", ""))

    Is there another way to rewrite this as excel is not allowing this because there are too many arguments

  10. Hi there,

    I kindly need help with an If function and wild cards returning different values based on if a job title contains a certain word as below

    =IF(C2="*Manager*", 100, IF(C2="*Director*", 100, IF(C2="*Specialist*", 20, 0))).......

    Since the F function dosn't work with Wild cards how can i execute this please

    Many thanks
    Barack

    1. Hello!
      To determine the presence of a certain word in the text, instead of C2=”*Manager*” try to use SEARCH function:

      ISNUMBER(SEARCH("Manager",C2))

      If the word is found it will return TRUE

  11. Can you exclude a character, such as "the second character cannot be an "S" "?

  12. Great article! I'm wondering if you can expand on the options of the =if(or(countif......

    I want different values if true or false. example

    =IF(OR(COUNTIF(A2, "*apple*"), "fruit", ""),(COUNTIF(A2, "*lettuce*"), "Veggie", ""),COUNTIF(A2, "*potatoe*"), "root", "") etc

    Is this possible?

    Thanks!

  13. Hi thanks for these tutorials
    I just wonder instead of marking yes or no I want the opposite cells to be marked as check mark ☑ or X mark but it won't work could it be possible? Thanks though ☺

    1. Hi Cheryl,

      For a check mark, you can use CHAR(252) or CHAR(254)
      For a cross mark, CHAR(251) or CHAR(253)

      Given the above, this formula:

      =IF(COUNTIF(A2, "*a*"), "yes", "no")

      can be transformed into this one:

      =IF(COUNTIF(A2, "*a*"), CHAR(252), CHAR(251))

      Note! For the check symbol and cross mark to display correctly, the Wingdings font should be set for the formula cells.

      If you are curious to learn more, you may find this tutorial helpful: 6 easy ways to insert a tick symbol and cross mark in Excel

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)