Exceljet

Quick, clean, and to the point

If cell is x or y and z

Excel formula: If cell is x or y and z
Generic formula 
=IF(AND(OR(A1=x,A1=y),B1=z),"yes","no")
Summary 

To apply more complex logic like "if cell is (x OR y) AND z", you can embed the AND and OR functions directly in the logical test of the IF function. In D5, the formula is:

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

This formula returns "x" if the color in B5 is either "red" or "green", and the quantity in C5 is greater than 10. Otherwise, the formula returns an empty string ("").

Explanation 

In the example shown, we want to "mark" or "flag" records where the color is either red OR green AND the quantity is greater than 10. The core of the formula is the logical test inside the IF function, which looks like this:

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

Note that the OR function appears inside the AND function. In other words, 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 an empty string "" (nothing) when the result is false.

Note: if we didn't supply an empty string, the formula would return FALSE whenever the logical test returned FALSE.

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.

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.