The goal is to do something if a cell equals a given value. The most common way to solve this problem is with the IF function.
The IF function runs a logical test and returns one value for a TRUE result, and another value for a FALSE result. The generic syntax for IF looks like this:
The result from IF can be a value, a cell reference, or even another formula. In the worksheet shown, the goal is to identify rows where the color is "Red" by returning "x" as a marker. To accomplish this task, the formula in cell F5 is:
In this formula, the logical test is this expression:
This expression returns TRUE if the value in C5 is "red" and FALSE if not. In cell F5, the result will be TRUE because C5 equals "red" but in cell F6 the result will be FALSE because C6 equals "Blue":
C5="red" // returns TRUE C6="red" // returns FALSE
The formula at this point looks like this:
Next, we need to add a value when the result is TRUE and a value when the result is FALSE. Since we want to mark items when the color is "Red", we provide "x" for the value to return if TRUE:
Since we don't want to display anything when the color is not "Red", we provide an empty string (""), for the value to return if FALSE. The final formula in cell F5 looks like this:
The result returned by IF can be customized as needed. If an empty string ("") is not provided for value_if_false, the IF function will return FALSE when the color is not "Red". Note that Excel is not case-sensitive by default. The expressions below will all return TRUE:
C5="Red" // returns TRUE C5="RED" // returns TRUE C5="red" // returns TRUE
If you need a case-sensitive formula, see the EXACT function.
Increase price if color is red
The result from IF does not need to be a hard-coded value. It can be a cell reference or another formula. For example, let's say you want to increase the price of Red items only by 10%. In that case, you can use a formula like this:
The test is the same as before (B6="red"). If the result is TRUE, we multiply the original price by 1.1 (i.e. increase the price by 10%). If the result is FALSE, we return an empty string ("").