Hadi Ul Bashar
In our day-to-day life, we have to make some decisions based on ‘IF’ and ‘Then’.For this task, Excel has a cool feature named IF for this purpose and we can extend its use even for multiple words. In this article, I’m going to share some handy examples of the use of the IF statement that contains multiple words and explain them so that you may use them whenever necessary.
A Quick Review of the IF Function:
IF checks whether a condition is met and results in one value if TRUE and another value if FALSE.
The syntax is
For further details, you may check this.
Download Practice Workbook
You can download the file from the link below.
4 Methods to Prepare IF Statement Contains Multiple Words in Excel
In the following sections, you will see several ways to prepare the IF statement that contains multiple words in Excel. Let’s check those.
Method 1: Use of Nested IF Function to Check Multiple Conditions
The example below comes up with the “Total Paid” section in column E. We have to decide the course of action based on the “Total Paid” values. The condition is pretty straightforward; if the “Total Paid” value is greater than $300 but less than $500, then the corresponding “Comment” cell value should hold a value of “Give reminder”. The other two conditions are in that order too and quite self-explanatory.
Steps:
- We’ll use the IF function in the column to get the desired output
- Type the following formula in the F7 cell:
=IF(E7>=700,"Limit Reached",IF(E7>=500,"Payment Required",IF(E7>300,"Give Reminder","Visit Buyer")))
- Hit Enter. This shows the output in cell F7.
- Hold and Drag down the AutoFill icon from the bottom right edge of cell F7 all the way to cell F12. This will automatically imply the formula we’ve used in cell F7 and make necessary adjustments to all the cells from F8 to F12.
We can see the output in the “Comment” column.
Read More: Show Cell Only If Value Is Greater Than 0 in Excel (2 Examples)
Method 2: Multiple Strings Search with IF Function
In this case, we’ve got a list of colored pencils with multiple types like wax and oil. Now, we want to detect a particular type of pencil, “Red Wax” to be specific. We’ll use a combination of the IF, AND, ISNUMBER, and SEARCH functions. You may check the highlighted functions for detailed use of them.
Steps:
- First of all, write the following formula in the C7 cell:
=IF(AND(ISNUMBER(SEARCH("Red",B7)),ISNUMBER(SEARCH("Wax",B7))),"Red Wax Detected ","Not Found")
Formula Breakdown
- SEARCH(“Red”,B7) > Here SEARCH function is looking for the position of substring Red in cell B7
Output: 1 - ISNUMBER(1) > ISNUMBER checks whether the argument is a number or not
Output: TRUE - SEARCH(“Wax”,B7) > Here SEARCH function is looking for the position of substring Red in cell B7
Output: 5 - ISNUMBER(5) > ISNUMBER checks whether the argument is a number or not
Output: TRUE - AND(TRUE,TRUE) > AND function has two TRUE arguments.
Output: TRUE - IF(TRUE,”Red Wax Detected “,”Not Found”) > Finally, IF will get this type of arguments within it and will return depending on the logic.
Output: Red Wax Detected
- The output looks like this:
- AutoFill is performed from cell C8 to cell C12
- Finally, the dataset is filled with all the desired output.
That’s it, we’ve written an IF statement that contains multiple words in Excel.
Read More: How to Use Conditional Formatting If Statement Is Another Cell
Similar Readings
- How to Use IF Statement with Yes or No in Excel (3 Examples)
- 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
Method 3: Matching Any String with IF and TEXTJOIN
In this example, we have a dataset of “Functions” which contains multiple function names in a single cell. Now, we have set particular function names to look for: “IFERROR”, “AND” and “LEN”. We want to look for these names in the “Functions” column and return the name of the present function’s name(s) in the corresponding cells of the “Comment” column.
For this reason, we’ve used the IF, COUNTIF, and TEXTJOIN functions. I’ll share the context here although you may check the details in the mentioned articles.
This is the dataset on which we’ll work.
Steps:
- Type the following formula in the C7 cell and hit Enter.
=TEXTJOIN(", ", TRUE, IF(COUNTIF(B7, "*"&$E$7:$E$9&"*"), $E$7:$E$9, ""))
I’ve explained the calculations here (The Active Function is BOLD form and the Output is in BOLD & ITALIC format):
- COUNTIF function returns the number of times a singular entity is present in a predefined range. Here, we’re looking for “IFERROR”, “AND” and “LEN” which span cells E7 to E9. Here Absolute Reference is used to keep this particular part of the formula unchanged in any situation.
- We’re applying the COUNTIF function in cell B7, this is the first argument of the function COUNTIF.
=TEXTJOIN(“, “, TRUE, IF(COUNTIF(B7, “*”&$E$7:$E$9&”*”), $E$7:$E$9, “”))
- The following calculation is shown here:
=TEXTJOIN(“, “, TRUE, IF(COUNTIF($B$7, {“*IFERROR”;”*AND”;”*LEN”}&”*”), $E$7:$E$9, “”))
- Next line:
=TEXTJOIN(“, “, TRUE, IF(COUNTIF($B$7, {“*IFERROR”;”*AND”;”*LEN”}&”*”), $E$7:$E$9, “”))
- The output of the COUNTIF function shows that IFERROR and AND are present in the B7 cell; both one time only, that’s why it returned {1:1:0}. Now, the IF function is active.
=TEXTJOIN(“, “, TRUE, IF({1;1;0}, $E$7:$E$9, “”))
- The output of the IF function returns the values “IFERROR” ;“AND”; “ ” as IFERROR and AND are searched in $E$7:$E$9. Now, the TEXTJOIN function is active.
=TEXTJOIN(“, “, TRUE, {“IFERROR”;”AND”;” “})
- The task of the TEXTJOIN function is combining the text from multiple ranges and or strings. The output looks like this. And it matches our target.
IFERROR, AND
- We can see the output in cell C7.
- AutoFill is performed from cell C8 to cell C11
- Finally, the dataset is filled with all the desired output.
Read More: How to Use IF Function with OR and AND Statement in Excel
Method 4: IF to Check Presence of a String in Text
Let’s check another way of writing the IF statement that contains multiple words in Excel. In this example, we’re going to check the presence of a string in a text using the IF function. Our dataset contains a column named “Functions” which contains several function names in each cell. We are trying to search the intended strings (“IFERROR”, “AND”, “LEN”) in column B and if any of them are present in column B, we want to get returned “Present” in the cells in the same row next to them, “Absent” otherwise.
For this purpose, we’ve used IF, ISNUMBER, OR, and SEARCH functions. We may check the listed articles if you need to go through them.
Let’s have a look at our dataset. Then we’ll start the process.
Steps:
- In cell C7, type the following formula and press Enter.
=IF(OR(ISNUMBER(SEARCH("IFERROR",B7)),ISNUMBER(SEARCH("LEN",B7)),ISNUMBER(SEARCH("SUM",B7))),"Present","Absent")
The calculation is shown here (The Active Function is BOLD form and the Output is in BOLD & ITALIC format)
- SEARCH function in bold is being evaluated here. Here SEARCH function is looking for
the position of substring IFERROR in cell B7
=IF(OR(ISNUMBER(SEARCH(“IFERROR”,B7)),ISNUMBER(SEARCH(“LEN”,B7)),ISNUMBER(SEARCH(“SUM”,B7))),”Present”,”Absent”)
- The B7 cell value is passed in the argument of SEARCH function
=IF(OR(ISNUMBER(SEARCH(“IFERROR”,”IF, AND IFERROR”)),ISNUMBER(SEARCH(“LEN”,B7)),ISNUMBER(SEARCH(“SUM”,B7))),”Present”,”Absent”)
- SEARCH function returns the value 10, which is now working as the argument of bold ISNUMBER:
=IF(OR(ISNUMBER(10),ISNUMBER(SEARCH(“LEN”,B7)),ISNUMBER(SEARCH(“SUM”,B7))),”Present”,”Absent”)
- Now, As, 10 is a number, so, ISNUMBER returns TRUE. At this stage, bolded SEARCH is active, and the process described above is repeated.
=IF(OR(TRUE,ISNUMBER(SEARCH(“LEN”,B7)),ISNUMBER(SEARCH(“SUM”,B7))),”Present”,”Absent”)
- The content of the B7 cell is now in the argument of SEARCH function
=IF(OR(TRUE,ISNUMBER(SEARCH(“LEN”, “IF, AND, IFERROR”)),ISNUMBER(SEARCH(“SUM”,B7))),”Present”,”Absent”)
- SEARCH function returns #VALUE which means “LEN” is not present in B7. Now, the ISNUMBER function is activated.
=IF(OR(TRUE,ISNUMBER(#VALUE!),ISNUMBER(SEARCH(“SUM”,B7))),”Present”,”Absent”)
- ISNUMBER function returns FALSE which means its argument is not a number. Now, the SEARCH function is activated.
=IF(OR(TRUE,FALSE,ISNUMBER(SEARCH(“SUM”,B7))),”Present”,”Absent”)
- Output of the SEARCH function is found, and we got it as explained above.
=IF(OR(TRUE,FALSE,ISNUMBER(SEARCH(“SUM”,“IF, AND, IFERROR”))),”Present”,”Absent”)
- Output of the SEARCH function is also explained above.
=IF(OR(TRUE,FALSE,ISNUMBER(#VALUE!)),”Present”,”Absent”)
- Output of the ISNUMBER function is FALSE and also explained before. Now OR function is active.
=IF(OR(TRUE,FALSE,FALSE),”Present”,”Absent”)
- Output of the OR function is TRUE as at least one argument of the function is TRUE. Now IF function is active.
=IF(TRUE,”Present”,”Absent”)
- Finally, we’ve got the output Present. This means the looked-up string(s) are present in cell B7.
Present
- The output is shown in the cell.
- AutoFill is performed from cell C8 to cell C11
- Finally, the dataset is filled with all the desired output.
Read More: How to Check If Cell Contains One of Several Values in Excel
Conclusion
If you’re in this segment, I thank you for your interest in this content. I’ve demonstrated 4 ways of solving problems where the IF statement contains multiple words in Excel. I hope you get the necessary solution. Being said that, if you face any problem regarding this article or have any queries, please feel free to leave a comment in the comment box below, Exceldemy team will try to solve that for you. Have a good day!
