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.
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.
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.
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.
Similar Readings
- How to Use Multiple IF Statements in Excel Data Validation
- Excel IF Statement with VLOOKUP for Multiple Conditions Range
- How to Use If Statement Based on Cell Color in Excel (3 Examples)
- Dynamic Data Validation List in Excel with IF Statement Condition
- How to Use IF Statement with Not Equal To Operator in Excel
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.
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.
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.
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.
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.
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
- Use Conditional Formatting If Statement Is Another Cell
- How to Use If Then Else Statement in Excel VBA (4 Examples)
- Excel IF Statement Between Two Numbers (4 Ideal Examples)
- Find Sum If Cell Color Is Green in Excel (4 Easy Methods)
- How to Prepare IF Statement Contains Multiple Words in Excel
- Show Cell Only If Value Is Greater Than 0 in Excel (2 Examples)
