If this AND that

The goal is to mark records with an "x" when the color is "Red" and the size is "Small". To perform this task, you can use the IF function in combination with the AND function.

IF function

The IF function runs a test, then returns one value if the result is TRUE, and a different value if the result is FALSE. The generic syntax for IF looks like this:

=IF(logical_test,value_if_true,value_if_false)

For example, if cell A1 contains the value 75, then you could use IF to return "Pass" or "Fail" like this:

=IF(A1>70,"Pass","Fail") // returns "Pass"

If the value in A1 is 65, then IF returns "Fail":

=IF(A1>70,"Pass","Fail") // returns "Fail"

Notice that the text values inside IF must be enclosed in double quotes ("").

AND function

The AND function returns TRUE if all arguments are TRUE. For example, if cell A1 contains "Red" and B1 contains 10, then:

=AND(A1="Red",B1>5) returns TRUE
=AND(A1="Red",B1>12) returns FALSE
=AND(A1="Blue",B1>5) returns FALSE

Because the AND function returns TRUE or FALSE, it often appears inside the IF function as a logical test.

IF + AND

In the example shown, we want to identify records where the color is Red and the size is Small. This means we need to test the value in column B for "Red" and test the value in column C for "Small". We can do that with the AND function like this:

AND(B6="red",C6="small")

Note: Excel formulas are not case-sensitive by default, so B6="red", B6="Red", and B6="RED" will all return the same result. For that reason, the text values in the logical test are left in lowercase.

The formula above will return TRUE only if the value in B6 is "Red" AND the value in C6 is "Small". If either condition isn't true, the AND function will return FALSE. To process the result from AND and mark a row with an "x" when both conditions are TRUE, we embed the AND formula inside the IF function as the logical test. In D6, the formula is:

=IF(AND(B6="red",C6="small"),"x","")

If the test is FALSE,  we don't want to display anything, so we simply return an empty string (""), which looks like an empty cell in Excel. As the formula is copied down, IF returns an "x" in column D only when the color is Red and the size is Small.

Note: You might wonder if we need to supply an empty string ("") for the false result. Technically, this argument is optional. However, if we don't provide a value, the IF function will return and display FALSE in all rows that aren't Red and Small.

Multiple conditions in the same cell

In some scenarios, you may need to evaluate multiple conditions within a single cell. For instance, if you need to verify that a cell value falls within a specific range (e.g., between 100 and 200), you can use the AND function as follows:

=AND(A1>=100,A1<200)

To mark values that meet these conditions, you could use the IF function as before:

=IF(AND(A1>=100,A1<200),"x","")