Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

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


Download Practice Workbook


3 Practical Examples of Using IF & OR Functions with Text in Excel

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.


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. You can also have a look at our other useful articles on Excel functions and formulas on our website, ExcelDemy, a one-stop Excel solution provider.

Zahid

Zahid

Hello and welcome! Thank you for visiting my profile. I am currently employed as an Excel & VBA Content Creator at ExcelDemy. My most recent academic qualification is a BSc (Eng) from the Bangladesh University of Engineering and Technology. Industrial and Production Engineering was my major. I constantly attempt to think creatively and find a simple answer.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo