If you want to use multiple conditions in an IF function in Excel, you have to use the AND or the OR function to evaluate all the conditions (two or more conditions).
All conditions evaluated have to be TRUE to make the output of the AND function TRUE.
Only one condition have to be TRUE to make the output of the OR function TRUE
Example: =IF(AND(condition1,condition2,condition3), ValueIfTrue,ValueIfFalse) =IF(OR(condition1,condition2,condition3), ValueIfTrue,ValueIfFalse)
AND and OR functions
AND and OR are the logical function used in all programming languages. Here is how these functions work.
The AND function
- AND (TRUE, TRUE) = TRUE
- AND (TRUE, FALSE) = FALSE
- AND (FALSE, TRUE) = FALSE
- AND (FALSE, FALSE) = FALSE
As you can see, both conditions have to be TRUE for the AND function to return a TRUE value.
In the example below, column D highlights the students with the best grade (A) in both terms. The formula is: =IF(AND(B2=”A”,C2=”A”),”yes”,””)
The OR function
- OR (TRUE,TRUE) = TRUE
- OR (TRUE, FALSE = TRUE
- OR (FALSE, TRUE) = TRUE
- OR (FALSE, FALSE) = FALSE
As you can see, both conditions have to be FALSE for the OR function to return a TRUE value. If any of the conditions is TRUE, the return value would be TRUE.
Using the example above, we added another column that highlights the students that have an A in at least one of the terms. The same formula is used but AND is replaced with OR. Notice the difference. =IF(OR(B2=”A”,C2=”A”),”yes”,””)
