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.

Example to apply OR function and AND function with the IF function

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(

Select the OR function Excel
  1. 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%

Entering logical values - 1st logical value
  1. 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%

Entering logical values - 2nd logical value
  1. 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.

The OR function evaluate arguments for two values and return true or 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%)

Using the XOR instead of OR function

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;

Selecting the IF function to apply the OR function in Excel
  1. 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%)

Insert the OR function as the test argument of the IF function in Excel.
  1. 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”

Providing the value to be returned if the logical test result is True.

If you want to enter text values for the arguments, you have to enter them within quotes. However, you don’t need to enter True or false values within quotes. Excel automatically evaluates arguments provided with true or false values.

Kasper Langmann, Microsoft Office Specialist
  1. 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”

Providing the value to be returned if the logical test result is False.
  1. Close the parentheses and press “Enter”.
Combining IF and OR functions in Excel to get the expected value.

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(

Entering the AND function in Excel.
  1. Enter all logical values to test using logical operators.

So, you can enter the following formula.

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

Entering arguments to test multiple conditions
  1. Close the parentheses and press “Enter”.
The AND function example

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;

Selecting the IF function
  1. 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%)

  1. 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”

Providing the value to be returned if the logical test result is True - Excel AND function
  1. 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”

Providing the value to be returned if the logical test result is False - Excel AND function
  1. Close the parentheses and press “Enter”.
Combining IF and AND functions in Excel to get the desired result.

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

Any empty cells in a logical function’s argument are ignored.

Kasper Langmann, Microsoft Office Specialist
Any empty cells in a logical function's argument are ignored

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

You can enter 2 conditions or more for the logical test argument of the IF function using the following formulas.

If you want to satisfy,

  • Both conditions – use the AND function.
  • At least one condition – use the OR function
  • Only one condition – use the XOR function.

Yes. We can use multiple logical functions with the IF function in Excel.

You can combine OR and AND functions to evaluate the arguments appropriately.