Summary

To test if a cell is equal to a given value, you can use the IF function. In the example shown, the formula in cell F5 is:

=IF(C5="red","x","")

The result in cell F5 is "x" since the color in cell C5 is equal to "Red". The result from IF can be adjusted as needed. See below for a formula to increase the price of "Red" items by 10%.

Generic formula

=IF(A1="red","x","")

Explanation 

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.

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:

=IF(logical_test,if_true,if_false)

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:

=IF(C5="red","x","")

In this formula, the logical test is this expression:

C5="red"

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:

IF(C5="red",

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:

IF(C5="red","x",

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:

=IF(C5="red","x","")

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:

=IF(C5="red",D5*1.1,"")

IF function example - increase price 10% if color is Red

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 ("").

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.