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

Get FREE Advanced Excel Exercises with Solutions!

In Microsoft Excel, the IF function is used to check if a condition is met, and then the defined statements will be shown based on the given situation. On the other hand, the wildcard is a symbol used if a cell contains the partial text. If you want to use the wildcard in the IF statement, you cannot use it directly because this function does not provide wildcard features like the COUNTIF, SUMIF, and AVERAGEIF functions. You may be upset after hearing that you cannot use the wildcard for partial text in the IF statement in Excel. But in this article, we’re going to show you the simple methods for using the wildcard in the IF statement in Excel. Don’t get upset. Go through the article and use the methods. So, let’s get started.


Download Practice Workbook

Download the following practice workbook. It will help you to realize the topic more clearly.


5 Methods to Use Wildcard with IF Statement in Excel

As we mentioned, you cannot use the wildcard in the IF formula. But you can use it in conjunction with other functions. To apply the methods, we have taken a dataset of some Product Lists of ABC Company. Now, we will look for a specific partial text along with the wildcards. It can be done with the IF function.

Sample Dataset

Not to mention, we have used the Microsoft 365 version. You may use any other version at your convenience.


1. Using IF and COUNTIF Functions for Wildcards

You can use the COUNTIF function in the IF statement to use wildcards. The conditional function COUNTIF counts the wildcards and then returns the value for the next step. Follow the below instructions to use it.

πŸ“Œ Steps:

  • Initially, move to cell D5 and insert the formula.
=IF(COUNTIF(C5, "*a*"), "Match", "Not Match")

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. Then the IF function takes 1 as TRUE, and 0 as FALSE. The IF function displays β€œMatch” for argument TRUE and β€œNot Match” for argument FALSE.

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

Read More: How to Check If Cell Contains One of Several Values in Excel


2. Utilizing IF & OR Functions

The OR function takes a value from two or more arguments and returns the boolean value as the logical_test argument for the IF function. Follow the steps to use these functions.

πŸ“Œ Steps:

  • Firstly, move to cell D5 and write up the below formula.
=IF(OR(COUNTIF(C5, "*a*"), COUNTIF(C5, "*b*")), "Yes", "")

It works for two partial matches. If you want to find two different partial matches, you need to use the OR function to add the texts. The COUNTIF function returns logical 1 and 0 for β€œMatch” and β€œNot Match” respectively. Lastly, the IF function returns β€œYes” or blanks for logical TRUE or FALSE output.

Eventually, 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

Read More: How to Use IF Statement with Yes or No in Excel (3 Examples)


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:

  • Primarily, in cell D5, insert the below formula.
=IF(ISNUMBER(SEARCH("A", C5)), "Match", "Not Match")

Formula Breakdown:

  • The SEARCH(β€œA”, C5) syntax searches for the letter β€œA” in cell C5 and returns the relative position of the letter.
  • The ISNUMBER(SEARCH(β€œA”, C5)) syntax 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, then it displays β€œMatch”. On the other hand, if the text is FALSE, it displays β€œNot Match”.

Apparently, press ENTER and get the following result.

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


Similar Readings


4. Employing ISNUMBER and FIND Functions

The FIND function also works the same as the SEARCH function. It returns the position for the argument and returns it to the logical test for the IF function. Follow the steps to use this function with wildcards.

πŸ“Œ Steps:

  • Initially, in cell D5, insert the below formula.
=IF(ISNUMBER(FIND("A", C5)), "Match", "Not Match")

Formula Breakdown:

  • The FIND(β€œA”, C5) syntax finds the letter β€œA” in cell C5 and returns the relative position of the letter.
  • The ISNUMBER(SEARCH(β€œA”, C5)) syntax 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 then it displays β€œMatch”. On the other hand, if the text is FALSE, it displays β€œNot Match”.

Subsequently, press ENTER and get the result below.

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


5. Incorporating IF & AND Functions

The AND function takes multiple arguments and checks if all the arguments are TRUE or FALSE. Then the returned value is used by the IF function. Follow the below steps to apply it.

πŸ“Œ Steps:

  • Firstly, move to cell D5 and input the formula.
=IF(AND(ISNUMBER(SEARCH("b", C5)), ISNUMBER(SEARCH("6", C5))), "Match", "")

Here, the AND function joins the results returned by the ISNUMBER and the SEARCH functions. Then the IF formula takes the returned value as logical_test. When the logical_test argument is TRUE, it displays β€œMatchβ€œ, and when the value is FALSE, it remains blank.

Consequently, press ENTER and drag down it for other cells to get the below output.

Incorporating IF and AND functions

Read More: How to Use IF Function with OR and AND Statement in Excel


Why Wildcard with IF Statement Is Not Working

Suppose you want to know the Product IDs matched with β€œaβ€œ. If the letter is found, then show β€œMatch” otherwise display β€œNot Matchβ€œ. We will insert a wildcard into the IF formula to accomplish our task. The formula is stated below.

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

Eventually, press ENTER and see the output.

But alas! 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

What could be the reason for this?

The answer is that Excel can’t recognize wildcards matched with an equal sign or any other arguments. We have provided a probable solution regarding this issue.


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

There are several conditional functions that provide the wildcard in the arguments. You can use those logical operators in your IF statement to get the ultimate result. The formula will be

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

Here, the COUNTIF function counts for the letter β€œa” bestowed with the wildcard asterisk (*). 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.

Fixation of Wildcard is not working in the IF statement in Excel

So, you can use the COUNTIF function in the IF formula to use the wildcard.


Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it yourself.

Practice Section


Conclusion

That’s all about today’s session. And these are some easy methods to use the wildcard in an IF statement in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding, please download the practice sheet. Visit our website, ExcelDemy, a one-stop Excel solution provider, to find out about diverse kinds of Excel methods. Thanks for your patience in reading this article.


Related Articles

Fahim Shahriyar Dipto

Fahim Shahriyar Dipto

Hello! Welcome to my Excel blog! I am a big fan of MS Excel. I am learning new and exciting things in Excel and writing the process here. I think this will be helpful for you to get used to Excel. Keep visiting our website for new and updated Excel methods.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo