If cell is this OR that

In the example shown, we want to mark or "flag" records where the color is red OR green. In other words, we want to check the color in column B, and then leave a marker (x) if we find the word "red" or "green". In D6, the formula is:

=IF(OR(B6="red",B6="green"),"x","")

This is an example of nesting – the OR function is nested inside the IF function. Working from the inside out, the logical test is created with the OR function:

OR(B6="red",B6="green") // returns TRUE

OR will return TRUE if the value in B6 is either "red" OR "green", and FALSE if not. This result is returned directly to the IF function as the logical_test argument. The color in B6 is "red" so OR returns TRUE:

=IF(TRUE,"x","") // returns "x"

With TRUE as the result of the logical test, the IF function returns a final result of "x".

When the color in column B is not red or green, the OR function will return FALSE, and IF will return an empty string ("") which looks like a blank cell:

=IF(FALSE,"x","") // returns ""

As the formula is copied down the column, the result is either "x" or "", depending on the colors in column B.

Note: if an empty string ("") is not provided for value_if_false, the formula will return FALSE when the color is not red or green.

Increase price if color is red or green

You can extend this formula to run another calculation, instead of simply returning "x". For example, let's say you want to increase the price of red and green items only by 15%. In that case, you can use the formula in column E to calculate a new price:

=IF(OR(B6="red",B6="green"),C6*1.15,C6)

The logical test is the same as before. However, the value_if_true argument is now a formula:

C6*1.15 // increase price 15%

When the result of the test is TRUE, we multiply the original price in column C by 1.15, to increase by 15%. If the result of the test is FALSE, we simply return the original price. As the formula is copied down, the result is either the increased price or the original price, depending on the color.

Notes

1. The IF function and the OR function are not case-sensitive.
2. The IF function can be nested inside itself.
3. Text values like "red" are enclosed in double quotes (""). More examples.