Excel Formula Using AND and OR Functions Multiple Conditions

There is a price list with discounts for certain product groups. Each product's name encodes information in 3 parts:

  1. Department code.
  2. Product type: ELM – element, PFB – semi-finished product, FIN – final product type.
  3. Unique identifier code.

A 19.25% discount applies only to FIN products from departments 202 and 203.



Logical Functions IF, AND, and OR for Analyzing Multiple Conditions

Below is a screenshot of the price list table with a formula determining the discount:

=IF(AND(OR(LEFT(A2,3)="202",LEFT(A2,3)="203"),MID(A2,5,3)="FIN"),19.25%,0%)
IF AND OR with multiple conditions.

The image clearly shows that the product with the code "201-FIN-4918" in cell A3 does not receive a discount, even though it is a final product, because its department code is 201, not 202 or 203. Meanwhile, products in cells A10 and A11 (with codes 202-FIN-3427 and 203-FIN-9969, respectively) receive discounts because each of them meets one of the conditions – 202 or 203. The fragment of the OR formula =OR(LEFT(A2,3)="202",LEFT(A2,3)="203") returns TRUE for these cells since one of the arguments in the OR function is true:

  • For cell A10 – the first argument is true: LEFT(A2,3)="202";
  • For cell A11 – the second argument is true: LEFT(A2,3)="203".

The logical expression in the IF function is structured to include analysis for assigning discounts based on multiple conditions. The AND function in the first argument is very strict and restrictive. For it to return TRUE, all its arguments must return TRUE. The OR function, on the other hand, is very lenient. It only requires one argument to return TRUE for the whole function to return TRUE. In the example above, the OR function is processed within the AND function. They work together in the logical expression of the IF function’s first argument. For clarity, the table below shows how these two logical functions, OR and AND, work together:

Logical Argument 1 (OR function)Logical Argument 2 (OR function)OR Function ResultLogical Argument 2 (AND function)Final Result
TRUETRUETRUETRUETRUE
TRUEFALSETRUETRUETRUE
FALSETRUETRUETRUETRUE
FALSEFALSEFALSETRUEFALSE
TRUETRUETRUEFALSEFALSE
TRUEFALSETRUEFALSEFALSE
FALSETRUETRUEFALSEFALSE

This schema is called a Truth Table and is often used in logic. It contains the results returned by logical expressions (TRUE or FALSE) depending on the logical arguments. Results in each row of the table are determined independently of the other rows. Truth tables are used to simplify logical expressions and analyze the logic behind them.

Alternative Formula with Multiple Conditions Without the IF Function

Interesting fact! In Excel, the logical value TRUE is 1, and FALSE is 0. Therefore, arithmetic operations can be performed with logical values in Excel. For example, TRUE+1=2 or FALSE+1=1.

Given this fact and analyzing the truth table, it can be seen that the IF function is not actually necessary for solving the problem. Simplify the formula by removing the IF function:

=AND(OR(LEFT(A2,3)="202",LEFT(A2,3)="203"),MID(A2,5,3)="FIN")*19.25%
formula without IF.

Download an example of using IF AND OR with multiple conditions in Excel download file

As shown in the image, we obtained the same result for checking values in column A based on multiple conditions.