1. Home
  2. >>
  3. LATEST
  4. >>
  5. Excel IF wildcard statement for partial text matching

Excel IF wildcard statement for partial text matching



Excel IF wildcard statement for partial text matching

Do you try to create an IF statement with wildcard text, but it always fails? The problem is not with your formula but with the function itself: Excel IF doesn’t support wildcards. However, there is a way to make it work for partial text matching, and this tutorial will teach you how to do it.

Whenever you want to perform fuzzy or partial matching in Excel, the most obvious solution is to use wildcards. But what if a specific function you need to use doesn’t support wildcards? Unfortunately, Excel IF is one of those functions. This is especially disappointing considering that other “conditional” functions like COUNTIF, SUMIF, and AVERAGEIF work perfectly fine with wildcards.

  Boffins invents the 42 GB DVD

Fortunately, it’s not the hurdle that can stop a creative Excel user 🙂 By combining IF with other functions, you can force them to evaluate a partial match and get a good alternative to an Excel wildcard IF formula.

Why the Excel IF function with wildcard does not work

In the sample table below, let’s say you want to check if the IDs in the first column contain the letter “A”. If found, show “Yes” in column B, if not, show “No”.

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

=IF(A2=”*a*”,”Yes”, “No”)

But unfortunately it doesn’t work. The formula returns “No” for all cells, even those that contain “A”:

  Serious irregularities in the ADSL providers in Spain

Why does a wildcard IF statement fail? Excel doesn’t seem to recognize wildcards that are used with an equals sign or other logical operators. Taking a closer look at the list of functions that support wildcards, you’ll notice that their syntax assumes that a wildcard text appears directly in an argument like this:

=COUNTIF(A2:A10, “*a*”)

Excel IF statement with wildcard text

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

IF(COUNT.IF(cell, “*text*”), value_if_true, value_if_false)

  candle review

With this approach, IF has no trouble understanding wildcards and perfectly identifies cells that contain “A” or “a” (since COUNTIF is not case sensitive):

=IF(COUNTIF(A2, “*a*”),”Yes”, “No”)

This formula goes to B2, or to any other cell in row 2, and then you can drag it to as many cells as you need:

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

=IF(COUNTIF(A2, “??-??”), “Valid”, “”)

How this formula works:

For the logical IF test, we use the COUNTIF function which counts the number of cells that match the specified wildcard string. Since the criteria range is a single cell (A2), the result is always 1 (match found) or 0 (no match found). Since 1 equals TRUE and 0 equals 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 matching is to include the FIND or SEARCH function in the logical test. The difference is that FIND is case sensitive, while SEARCH is not.

  GRIP, a video reveals the release date

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

It does not distinguish between upper and lower case 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)

Since both functions are designed to perform a “cell contains” type of match, wildcards aren’t really necessary in this case.

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

=IF(ISNUMBER(SEARCH(“A”, A2)), “Yes”, “No”)

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

=IF(ISNUMBER(FIND(“A”, A2)), “Yes”, “No”)

  Bejeweled 2 – Cheats

On B6 in the screenshot below, you can see the difference in output:

How this formula works:

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

ISNUMBER(SEARCH(“A”, A2))

The SEARCH function searches 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. Since both SEARCH and FIND are designed to perform a “cell contains” type of match, wildcards aren’t really necessary 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 IF test. In our case, A2 contains “A”, so ISNUMBER returns TRUE:

  Ape Academy 2

IF(TRUE, “Yes”, “No”)

As a 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 the 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 case and return “Yes” if one is found, use one of these formulas:

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

either

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

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

  Rugby 2004

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

Instead of encoding wildcard strings in the formula, you can enter them in separate cells, say D2 and F2, as shown in the screenshot below. Note that these cell references are locked with the $ sign so that the formula is correctly copied to the following cells:

=IF(OR(COUNTIF(A2, “*”&$D$2&”*”), COUNTIF(A2, “*”&$F$2&”*”)), “Yes”, “”)

The above formulas work fine for 2 partial matches, but if you’re looking for 3 or more, they’ll get too long. In this case, it is logical to approach the task differently:

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

  Echo Prime, review

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

This way, you will get exactly the same result with a more compact 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 logical testing.

Suppose you want to locate cells in column A that contain both “b” and “2”. To do this, use “*b*” and “*2*” for the COUNTIFS criteria and A2 for the range of criteria:

=IF(COUNTIFS(A2, “*b*”, A2, “*2*”), “Yes”, “”)

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

  QuarkXpress already has support for Pantone Goe

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

Although we do not include any wildcard characters in this formula, it works like searching for two wildcard strings (“*b*” and “*2*”) in the same cell.

Of course, nothing prevents you from entering the lookup values ​​into 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”, “”)

If you prefer to use more compact formulas whenever possible, you might be better off liking the constant array approach. The IF COUNT SEARCH formula is very similar to the previous example, but since both substrings must appear in A2 this time, we check if the count is equal to 2:

  Telegraph: “grown men shouldn’t waste their lives playing video games”

=IF(COUNT(SEARCH({“b”,”2″}, A2))=2, “Yes”, “”)

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

Workbook to download

(.xlsx file)