Excel formula: If cell is x or y and z

Generic formula

=IF(AND(OR(A1=x,A1=y),B1=z),"yes","no")

Explanation

You can combine logical statements with the OR and AND functions inside the IF function.

If color is red or green and quantity is greater than 10

In the example shown, we simply want to "mark" or "flag" records where the color is either red OR green AND the quantity is greater than 10.

In D5, the formula were using is this:

=IF(AND(OR(B5="red",B5="green"),C5>10),"x","")

How this formula works

In this formula, the logical test is this bit:

AND(OR(B5="red",B5="green"),C5>10)

Note that the "parent" function is AND The OR function is logical1 inside the AND function while C5>10 is logical2.

This snippet will return TRUE only when the color in B5 is either "red" OR "green" AND the quantity in C5 is greater than 10.

The IF function then simply catches the result of the above snippet and returns "x" when the result is TRUE and "" (nothing) when the result is false.

Note: if we didn't add the empty string when FALSE, the formula would actually display FALSE whenever the color is not red.

Author

Dave Bruns

Related formulas

If cell equals

If you want to do something specific when a cell equals a certain value, you can use the IF function to test the value, then do something if the result is TRUE, and (optionally) do something else if the result of the test is FALSE. If color is red,...

If cell is this OR that

If you want to do something specific when a cell equals this or that (i.e. is equal to X or Y, etc.) you can use the IF function in combination with the OR function to run a test, then take one action if the r esult is TRUE, and (optionally) do...

Related functions

Excel IF Function

The IF function can perform a logical test and return one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions....

Excel OR Function

The OR function is a logical function to test multiple conditions at the same time. OR returns either TRUE or FALSE. For example, to test A1 for either "x" or "y", use =OR(A1="x",A1="y"). The OR function can be used as the logical test inside the...

Excel AND Function

The Excel AND function is a logical function used to require more than one condition at the same time. AND returns either TRUE or FALSE. To test if a number in A1 is greater than zero and less than 10, use =AND(A1>0,A1...

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.