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:


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

Generic formula



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:


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.

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.