How to Use Wildcard with If Statement in Excel (5 Methods)

Download the Practice Workbook

The wildcard in IF Statement.xlsx

Why Wildcard with IF Statement Is Not Working

Consider the following function.

=IF(C5= "*a*", "Match", "Not Match")

The formula doesn’t give you the output you wanted. You can see this formula is showing “Not Match” even if there is an “a” in the IDs.

The wildcard is not working in the IF statement in Excel

Excel can’t recognize wildcards matched with an equal sign or any other operators.

5 Methods to Use Wildcard with IF Statement in Excel

You can’t use the wildcard in the IF formula alone, but you can use it in conjunction with other functions. We’ll use a dataset of Product Lists of ABC Company. We will look for a specific partial text with the wildcards.

Sample Dataset

Method 1 – Using IF and COUNTIF Functions for Wildcards

Steps:

Go to cell D5 and insert this formula:

=IF(COUNTIF(C5, "*a*"), "Match", "Not Match")

AutoFill to the other cells in the column.

The COUNTIF function is used as the logical_test for the IF function. The function takes the value of cell C5 and matches it with “*a*”. It returns 1 for a match and 0 for not matching. The IF function takes 1 as TRUE, and 0 as FALSE. The IF function then displays “Match” for TRUE and “Not Match” for FALSE.

Using IF and COUNTIF functions to use wildcard in IF statement in Excel

Method 2 – Utilizing COUNTIF and OR Functions

Steps:

Go to cell D5 and insert the following formula:

=IF(OR(COUNTIF(C5, "*a*"), COUNTIF(C5, "*b*")), "Yes", "")

If you want to find two different partial matches, you need to use the OR function to make two separate COUNTIF checks.

Press Enter and drag down to get the following output for other cells.

Utilizing IF and OR functions to use wildcard in IF statement in Excel

Method 3 – Applying ISNUMBER and SEARCH Functions

The SEARCH function returns the relative position of the argument. Then the ISNUMBER function returns the value in binary order. So it takes the partial text as its logical value. The procedure for using this formula is stated below.

Steps:

In cell D5, insert the following formula, apply it, and AutoFill through the column.

=IF(ISNUMBER(SEARCH("A", C5)), "Match", "Not Match")

Formula Breakdown:

SEARCH(“A”, C5)searches for the letter “A” in cell C5 and returns the relative position of the letter.
ISNUMBER(SEARCH(“A”, C5)) returns the logical TRUE if it finds “A” in cell C5 and vice-versa.
The returned value is then used as the logical_test of the IF formula. If the text is TRUE, it displays “Match”. If the text is FALSE, it displays “Not Match”.

Applying ISNUMBER and SEARCH function with a wildcard in an IF statement in Excel

Similar Readings

Method 4 – Employing ISNUMBER and FIND Functions

Steps:

Go to cell D5, insert this formula, apply it, and AutoFill the column.

=IF(ISNUMBER(FIND("A", C5)), "Match", "Not Match")

Formula Breakdown:

FIND(“A”, C5) finds the letter “A” in cell C5 and returns the relative position of the letter.
ISNUMBER(SEARCH(“A”, C5)) returns the logical TRUE if it finds “A” in cell C5 and vice-versa.
The returned value is then used as the logical_test of the IF formula. If the text is TRUE it displays “Match”.

Employing ISNUMBER and FIND functions for wildcard in IF statement in Excel

Method 5 – Incorporating IF and AND Functions

Steps:

Go to cell D5 and input the formula, then use AutoFill for the column.

=IF(AND(ISNUMBER(SEARCH("b", C5)), ISNUMBER(SEARCH("6", C5))), "Match", "")

The AND function joins the results returned by the ISNUMBER and the SEARCH functions. The IF formula takes the returned value as logical_test. When the logical_test argument is TRUE, it displays “Match“. When the value is FALSE, it remains blank.

Incorporating IF and AND functions

What to Do If Wildcard with IF Statement Is Not Working?

Use a wildcard as a part of a string in a formula that compares strings, such as COUNTIF:

=IF(COUNTIF(C5, "*a*"), "Match", "Not Match")

The COUNTIF function counts for the letter “a” inside wildcard asterisks (*). Then, the IF function takes the output of the COUNTIF function as a logical_test and returns “Match” if the argument meets the criteria. Otherwise, It returns “Not Match”. See the image below for a better understanding.