How to Use The IF and OR Functions Together in Excel

The IF function in Excel is the most commonly used when you want to create a conditional formula but it’s usually a very basic condition 🤔

To create a more complex condition, we combine IF with logical functions like OR. This lets you make decisions based on multiple criteria in a seemingly easy way 🧐

In this tutorial, we will see the IF and OR functions in action. To practice along the guide, download our free sample workbook here. That being said, let’s get right into it.

Table of Contents

IF OR functions in Excel

OR is a logical function and works with two values TRUE and FALSE. The OR function returns a TRUE value when at least one of the given conditions is true ✅

This means if you have a value in cell A2, say, 10, and you apply OR conditions to it as A2 > 15 or A2 = 10, the OR function will return TRUE. That’s because the second condition is true for cell A2.

The syntax of the OR function is given as:

where,

logical1 refers to the condition you want to check

Even though you can use the OR function on its own, it would only return TRUE or FALSE. It becomes twice as powerful and customizable as you combine it with the Excel IF function.

You can use logical functions with conditional formatting if you want to format cells to return different results 🤓

But if you want the function to return a text string, empty string, numbers, or result of an expression, IF is your go-to function.

The syntax of IF and OR combined is given as below:

where,

condition1 refers to the logical test you’re putting up

value_if_true refers to the value to be returned if the condition is true

value_if_false refers to the value to be returned if the condition is false

The logic of an IF and OR combined function is such that it checks multiple conditions and if any one of these conditions is fulfilled, it will display the value of the true argument otherwise the value of the false argument ❌

IF OR function examples

Let’s see some example cases for the IF and OR functions to understand how they work together.

Comparison test

Say, we have the following sample data of a class containing a student’s grades in different subjects 📗

We want to check if he has passed these subjects or not where the passing criteria is to have obtained more than 50% in the said subject.

To do that we will follow a step-by-step process:

Step 1) Select cell C2 in column C.

Step 2) Type in the following formula:

Step 3) Press Enter.

The function returns the result as Pass which is correct. To get the formula for the other value in column B, double-click the Fill Handle to copy the formula down 🔽

And it’s done! How easy was that? 😀

IF with multiple OR functions

You can incorporate as many OR conditions in your Excel formulas as you want as long as it doesn’t cross the 255 conditions limit.

However, it is not recommended to use too many because a complex nested OR formula becomes difficult to debug and maintain 🔍

Let’s use the following example for this case.

Say, we have a sample dataset where some items are being shipped. We want any order that’s not in the delivered category to be labelled complete while all others are incomplete ❗

To do this,

Step 1) Select cell C2.

Step 2) Type in the following formula:

Step 3) Press Enter.

To copy the formula down the remaining cells, simply double-click the Fill Handle ➕

Pro tip

Note that the function returned “Incomplete” for “Cancelled” even though in the formula the condition is to look for “CANCELLED”. This shows that the IF OR formula is case-insensitive as it does not differentiate between uppercase and lowercase.

IF OR formula with case-sensitivity

We just saw that the OR function is case-insensitive so what do you do if your data is case-sensitive? You couple it with the EXACT function 🔍

We will write all our conditions inside the EXACT function and then nest it all in the OR function.

The syntax for IF OR with EXACT is given as follows:

where,

cell refers to the cell reference containing the value

condition1 refers to the OR logical test to be checked

Let’s now see an example where IF OR and the EXACT function are working together.

Say, we have the following sample data where we have some product codes. A couple of these have been repeated by mistake but we only want CO-1 and MT-3.

So we will write a function that returns “Found” when the code is in the same otherwise “Not found” ❌

To do that,

Step 1) Select cell B2.

Step 2) Type in the following formula:

Step 3) Press Enter.

Copy the above formula down the remaining cells using the Fill Handle.

The formula checks for the exact match and then returns Found and not found if even a single character is different from the string given.

Cool, no? 😉

Return calculated result with IF OR

The IF formula also offers the luxury of returning calculated values in the true or false values. This adds an element of personalization as you are no longer restricted to text strings only 🧵

Let’s see an example of this case.

Say, you have the following sample data. It contains a list of salaries of a company’s employees. If the salary of an employee is above a certain threshold, he will be charged 10% tax. If not, then no tax.

To do this,

Step 1) Select cell B2.

Step 2) Type in the following formula:

Step 3) Press Enter.

Copy the formula down to the remaining cells by double-clicking the Fill Handle.

Voila! It’s done. The function calculates a 10% tax on salaries that exceed the 30000 threshold and returns a “No tax” text string for the values below the threshold.

How easy is that? Give it a try now 🧐

Nested IF OR example

Sometimes, you might need to get specific results for specific conditions all in one formula. In such cases, we use the nested If OR function.

It might look complicated but in actuality, it makes everything rather simple. Let’s see a simple example of it below 🔽

Say, we have the following sample data below. We want the function to check if a product lies in a certain category, if not then check for it in another category and display that.

To do that,

Step 1) Select cell B2.

Step 2) Type in the following formula:

Step 3) Press Enter.

Copy the formula down the remaining cells

And it’s done.

the function will now return Beverage when it finds the word Coffee or Water and Stationary when it spots Books or Pencil.

Pretty cool, no? 😀

IF OR with AND Logical function

You can also use AND and OR with IF functions to construct complicated functions for a specific problem. AND function returns TRUE only when all the conditions are true.

Let’s see an example below that involves IF AND OR functions.

We have the following sample data, where we want to check if sales are made by Ali or Henry and if the sales exceed $1000 💲💲

To do that,

Step 1) Select cell C2.

Step 2) Type in the following formula:

Step 3) Press Enter.

Copy the formula down the remaining cells by dragging down the Fill Handle or double-clicking it.

The functions return the result as expected.

The logic of this function is that it starts from the OR function and checks if the cell value is Ali or Henry, if true then it checks if the value in cell B2 is greater than 1000 or not.

If both conditions return true, the function returns Check, otherwise Uncheck. This also proves the classic AND behaviour that when in a function, it returns true only when all the conditions are true.

See how that simplifies things? Try it yourself now! 😃

Conclusion

In this Excel tutorial, we saw how to use IF and OR functions in combination we each other. We saw different use cases and methods in which you can use the IF and OR functions.

We also used the AND function with IF and OR to evaluate different combinations in one go. If you want to read more about IF OR or AND functions, read the articles below 🧾

How to Use the IF Function in Excel: Step-by-Step (2024)

How to Use the IF and AND Functions in Excel (Examples)

Excel AND + OR Functions: Full Guide (with IF Formulas)

We hope you enjoyed reading this article as much as we did creating it 🤗