How to Prepare IF Statement Contains Multiple Words in Excel

Get FREE Advanced Excel Exercises with Solutions!

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

=IF(logical_test, [value_if_true], [value_if_false])

For further details, you may check this.


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

Image of Dataset for Excel IF Statement contains multiple words

  • 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")))

Nested IF Formula Statement

  • Hit Enter. This shows the output in cell F7.

Output of the Formula

  • 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.

AutoFill column

We can see the output in the “Comment” column.

Output of AutoFill

Read More: Show Cell Only If Value Is Greater Than 0 in Excel 


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.

Image of Dataset

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 in Cell C7

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:

Output of the Formula

  • AutoFill is performed from cell C8 to cell C12

AutoFill other cells

  • Finally, the dataset is filled with all the desired output.

Final 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


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.

Image of Dataset

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, ""))

Formula in cell C7

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.

Output of the Excel IF Statemnet Formula

  • AutoFill is performed from cell C8 to cell C11

AutoFill column

  • Finally, the dataset is filled with all the desired output.

Output of AutoFill

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.

Image of Dataset for IF Statement

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")

IF Statement with multiple words Formula in cell C7

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.

Output of the Formula

  • AutoFill is performed from cell C8 to cell C11

AutoFill column

  • Finally, the dataset is filled with all the desired output.

If Statement Contains Multiple Words Output of AutoFill in Excel

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


Download Practice Workbook

You can download the file from the link below.


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 .Have a good day!


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Hadi Ul Bashar
Hadi Ul Bashar

Hadi Ul Bashar holds a BSc degree in Biomedical Engineering from Bangladesh University of Engineering and Technology.  In January 2023, Bashar joined SOFTEKO as an Excel and VBA content developer. Since then, he has authored over 50 articles. He was engaged in two specialized training programs focused on VBA and Chart & Dashboard designing in Excel. Currently he is creating video content on Excel and VBA and has created over 70 videos. Reading books and traveling are his... Read Full Bio

2 Comments
  1. Reply
    Danny Van Straten Jul 25, 2023 at 4:49 PM

    How do i setup a formula to Validate Column K(any value from 0-8;multiple lines (115) and then Display the corresponding value in Words(column T) in Column L.
    List in Column S and T
    1 Permanent Morning /Day shift
    2 Permanent Afternoon shift
    3 Permanent Night shift
    4 Rotational-Morning and Afternoon Shift
    5 Rotational-Morning and Night Shift
    6 Rotational-Afternoon and Night Shift
    7 Rotational-Morning ,Afternoon and Night Shift
    8 Employees who work different workschedules on the same Org unit

    If this make sense? tx

    • Dear Danny Van Straten,

      If you need any types of customized templates you may contact us through [email protected]
      We have a expert team to create any types of professional templates.

      Regards
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo