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

The AND and OR functions returns True or False if certain conditions are met.

Combined with other functions, like IF, that enables multiple criteria logic in your formulas.

In this guide, I’ll walk you through all of this, step-by-step 👍🏼

If you want to tag along, download the sample workbook here.

We use the data in the following table to learn how to apply OR and AND function with the IF function in excel.

The OR function

The OR function is one of the most important logical functions in excel 😯

The OR function in Excel returns True if at least one of the criteria evaluates to true.

If all the arguments evaluate as False, then the OR function returns False.

The syntax of the OR function in Excel is OR(logical1, [logical2], …).

Assume that employees are eligible for an incentive if they achieve a value or volume target of 75% or more 🏆

Let’s try OR function for the above example.

1. Enter an equal sign and select the OR function.

You will see below in the formula bar.

=OR(

2. Supply the first logical value to evaluate as the first argument.

You can write logical values to test using logical operators.

In this case, we want to first test, whether the value target achievement is greater than or equal to 75%.

So, we can give the cell reference and write the condition >=75%.

Now the formula is,

=OR(B3>=75%

3. Enter a comma and enter the 2nd logical value for the logical test.

Then, we enter the logical value for the volume target.

Now, the updated formula is;

=OR(B3>7=75%,C3>=75%

4. Close the parentheses and press enter.

The below Excel formula evaluates arguments.

=OR(B3>7=75%,C3>=75%)

Then returns true if at least one of the value or volume target achievements is greater than or equal to 75%. If both value and volume target achievements are below 75%, the OR function returns False.

Pro Tip:

Now you have learned, the OR function returns True even when more than one condition evaluates to “True”.

Do you think you have to combine the OR function with the NOT function?

No. Excel has a simple solution 😜

You have to use the XOR function.

Apply the below XOR formula to the above example and see how the results are changing.

=XOR(B3>7=75%,C3>=75%)

You can see that Mary’s result is “False” as her achievements are not satisfying the one and only condition.

OR function IF formula example

Isn’t it boring to see only “True or false” values? 🥱

Don’t you like to get something other than True or False values? 👍

You just need to combine the OR function with the IF function in Excel.

Let’s say, we want the function to return “Eligible” if at least one of the value or volume target achievements is greater than or equal to 75%.

Also, return “Not eligible”, if both value and volume target achievements are less than 75%.

1. Enter the equal sign and select the IF function.

Now, the formula bar will show;

2. Enter the OR function that we have learned in the previous section.

The updated formula should be like this.

=IF(OR(B3>=75%,C3>=75%)

3. Enter the specific value you want the function to return when the result of the logical test is True.

In this case, we want to get “Eligible”.

So, we enter the word eligible within quotes.

Now, the formula is;

=IF(OR(B3>=75%,C3>=75%),”Eligible”

4. Enter the specific value you want the function to return when the result of the logical test is False.

In this case, we want to get “Not Eligible”.

Now, the updated formula is;

=IF(OR(B3>=75%,C3>=75%),”Eligible”,”Not Eligible”

5. Close the parentheses and press “Enter”.

The AND function

AND function is another important logical function in excel.

This function returns true all of the multiple criteria are true.

Otherwise, the AND function returns False.

The syntax of the AND function in Excel is AND(logical1, [logical2], …).

Say that employees are entitled to an incentive if they achieve more than or equal to 75% for both the value target and volume target.

Let’s try AND function for the above example.

1. Enter an equal sign and select the AND function.

You will see below in the formula bar.

=AND(

2. Enter all logical values to test using logical operators.

So, you can enter the following formula.

=AND(B3>=75%,C3>=75%

3. Close the parentheses and press “Enter”.

You can see that the AND function returns true only when both the value and volume target achievements exceed or are equal to 75% 🥳

However, there is limited usage of AND function if we do not combine it with other functions in Excel 🤔

Let’s see an example with a combination of the IF function and AND function.

AND function IF formula example

Let’s say, we want the function to return “Eligible”, only when both value and volume target achievements are more than or equal to 75%.

Otherwise, we want to get “Not eligible”

1. Enter the equals sign and select the IF function.

Now, the formula bar will show;

2. Enter the AND function that we have learned in the previous section.

The updated formula should be like this.

=IF(AND(B3>=75%,C3>=75%)

3. Enter the specific value you want the function to return when the result of the logical test is True.

In this case, we want to get “Eligible”.

So, we enter the word eligible within quotes.

Now, the formula is;

=IF(AND(B3>=75%,C3>=75%),”Eligible”

4. Enter the specific value you want the function to return when the result of the logical test is False.

In this case, we want to get “Not Eligible”.

Now, the updated formula is;

=IF(AND(B3>=75%,C3>=75%),”Eligible”,”Not Eligible”

5. Close the parentheses and press “Enter”.

You can see that we get “Eligible” only when all of the multiple conditions are True. Otherwise, functions will return false 😍

That’s it – Now what?

Well done 👏🏻

Now you know how to use the OR and AND functions in Excel.

Using the above examples, you have learned that OR and AND functions are more powerful when we combine them with other Excel functions 💪🏻

To learn more about other important functions such as IF, SUMIF, and VLOOKUP functions, all you need to do is enroll in my free 30-minute online Excel course.

Other resources

When you are using OR and AND functions, it is extremely important to use logical operators correctly ⚡

Read our article about logical operators, if you want to refresh your knowledge about them.

Also, Read our articles about the IF function, nested IF function, and SUMIF function and apply OR and AND functions more effectively 🥳

Frequently asked questions