Exceljet

Quick, clean, and to the point

If cell is this OR that

Excel formula: If cell is this OR that
Generic formula 
=IF(OR(A1="this",A1="that"),"x","")
Summary 

To do something when a cell is this or that (i.e. a cell is equal to "x", "y", etc.) you can use the IF function together with the OR function to run a test. In cell D6, the formula is:

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

which returns "x" when B6 contains "red" or "green", and an empty string ("") if not. Notice the OR function is not case-sensitive.

Explanation 

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 were using 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 function example - increase price if color is red or green

=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 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.
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.