How to Use IF & OR Functions with Text in Excel (3 Examples)

Get FREE Advanced Excel Exercises with Solutions!

While working in Excel, sometimes we need to find exact and partial matches of a text. We can use the combination of the IF & OR functions to do this in Excel. In this article, we will discuss three practical examples of using the IF & OR functions with text in Excel. So, let’s start this article, and explore these examples.

Overview of methods to use the IF & OR functions with text in Excel


How to Use IF & OR Functions with Text in Excel: 3 Examples

In this section of the article, we will discuss three practical examples of using the IF & OR functions with text in Excel. Let’s say, we have the Work Schedule of ABC Company as our dataset. Our goal is to use the combination of the IF & OR functions together with text.

First sample dataset

Not to mention, we used the Microsoft Excel 365 version for this article; however, you can use any version according to your preference.


1. Combining Text Using IF & OR Functions in Excel

In the first example, we will combine texts using the IF & OR functions in Excel. Let’s say, we want to know whether a Day is a “Working Day” or it’s a “Weekend“. Here, if the day is Saturday or Sunday, we will show “The Day is Weekend” in cell D5. Otherwise, it will show, “The Day is Working Day“. Now, let’s follow the instructions mentioned below.

Steps:

  • Firstly, use the following formula in cell D5.
="The Day is "&IF(OR(C5="Saturday",C5="Sunday"),"Weekend","Working Day")

Formula Breakdown 

  • Here, in the OR function,
    • C5=”Saturday” → It is the logical1 argument.
    • C5=”Sunday” → This refers to the [logical2] augment.
    • Output FALSE.
  • Now, the IF function becomes → =IF(FALSE,”Weekend”,”Working Day”).
    • Here, FALSE → This is the logical_test argument.
    • “Weekend” → It refers to the [value_if_true] argument.
    • “Working Day” → This indicates the [value_if_false] argument.
    • Output “Working Day”.
  • Finally, two text strings are combined together by an And Operator (&).
    • Output The Day is Working Day.
  • Then, hit ENTER.

Using IF & OR functions to combine text in Excel

As a result, you will have the following output in cell D5.

Output obtained after using IF & OR function to combine text in Excel

  • Finally, use Excel’s AutoFill feature to get the rest of the outputs as demonstrated in the image below.

Using Autofill feature to get the rest of the outputs


2. Using IF & OR Functions with Text Having Exact Match

In the second example, we will use the IF & OR functions with texts that have exact matches. Here, we will use the same dataset that we used in the first example. For instance, let’s say, we want to show the Working Hours for every day. But if the day is Saturday or Sunday, then we will show “Weekend“. Now, let’s follow the steps mentioned below to do this.

Steps:

  • Firstly, use the following formula in cell D5.
=IF(OR(C5="Saturday",C5="Sunday"),"Weekend","9:00AM - 5:00PM")

Here, cell C5 represents the first cell of the Day column.

Formula Breakdown 

  • Here, in the OR function,
    • C5=”Saturday” → It is the logical1 argument.
    • C5=”Sunday” → This refers to the [logical2] augment.
    • Output FALSE.
  • Now, the IF function becomes → =IF(FALSE,”Weekend”,”9:00AM – 5:00PM”).
    • Here, FALSE → This is the logical_test argument.
    • “Weekend” → It refers to the [value_if_true] argument.
    • “9:00AM – 5:00PM” → This indicates the [value_if_false] argument.
    • Output 9:00AM – 5:00PM.
  • After that, press ENTER.

Using a combination of IF & OR functions with text in Excel

Consequently, you will have the Working Hour for Thursday in cell D5.

Output obtained after using IF & OR functions for exact match in cell D5

  • Finally, use Excel’s AutoFill feature to get the Working Hours for the rest of the Days.

Here, you can see that for Saturday and Sunday, “Weekend” is displayed in the Working Hour column, as demonstrated in the following picture.

Applying the AutoFill feature to get the remaining outputs 


3. Applying IF & OR Functions with Text Having Partial Match

In the previous example, we dealt with exact matches. But in this example, we will use the IF & OR functions with texts that have a partial match. Here, we will use wildcards to specify partial matches. Let’s say, we have the Laptop Prices in Micro Center Store as our dataset. We will check the Availability of the laptops in the inventory. Let’s say, all the Asus and Lenovo laptops are out of stock and the other laptops are in stock at this moment. So, we will display “Out of Stock” for Asus and Lenovo laptops and “In Stock” for the remaining laptops. Now, let’s use the instructions outlined below to do this.

Second sample dataset

Steps:

  • Firstly, apply the following formula in cell E5.
=IF(OR(COUNTIF(B5,"Asus"&"*"),COUNTIF(B5,"Lenovo"&"*")),"Out of Stock","In Stock")

Here, cell B5 indicates the first cell of the Laptop column.

Formula Breakdown 

  • In the first COUNTIF function,
    • B5 → It refers to the range argument.
    • “Asus”&”*” → It is the criteria argument.
    • Output 1.
  • In the second COINTIF function,
    • B5 → This indicates the range augment.
    • “Lenovo”&”*” → It is the criteria argument.
    • Output 0.
  • Now, the OR function becomes, OR(COUNTIF(B5,”Asus”&”*”),COUNTIF(B5,”Lenovo”&”*”)) →  OR(1,0).
    • Output TRUE.
  • Then, the IF function becomes, =IF(OR(COUNTIF(B5,”Asus”&”*”),COUNTIF(B5,”Lenovo”&”*”)),”Out of Stock”,”In Stock”) =IF(TRUE,”Out of Stock”,”In Stock”).
    • Here, TRUE →  This is the logical_test argument.
    • “Out of Stock” → It represents the [value_if_true] argument.
    • “In Stock” → This is the [value_if_false] argument.
    • Output Out of Stock.
  • Following that, press ENTER.

Using IF, OR, & COUNTIF functions for texts having partial match in Excel

Consequently, you will see that the Asus ZenBook UX430UN laptop has gone Out of Stock.

Output obtained after using IF & OR functions with Text in Excel 

  • Lastly, apply Excel’s AutoFill option to get the Availability for the remaining Laptops.

 Utilizing the AutoFill option to get the remaining outputs


Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it yourself.

Sample Practice Section provided in each worksheet of the Practice Workbook.


Download Practice Workbook


Conclusion

So, these are the most common and effective methods you can use anytime while working with your Excel datasheet to use the IF & OR functions with text in Excel. If you have any questions, suggestions, or feedback related to this article, you can comment below.


<< Go Back to Text | If Cell Contains | Formula List | Learn Excel

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.
Zahid Hasan
Zahid Hasan

Zahid Hassan, BSc, Industrial and Production Engineering, Bangladesh University of Engineering and Technology, has worked with Exceldmy for 1.5 years. He has written 95+ articles for Exceldemy. He has worked as an Excel & VBA Content Developer. He also worked as a VBA Developer for the Template team. Currently, he is working as a Junior Software Developer for the Excel Add-in project. He is interested in Software Development, Python, VBA, VB.NET, and Data Science, expanding his expertise in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo