Summary

To check two items with exclusive OR (one or the other, but not both), you can use the XOR function. In the example shown, E5 contains the following formula:

=XOR(C5="x",D5="x")

This formula returns TRUE when either coffee or tea has an "x". It returns FALSE if both coffee and tea have an "x", or if neither coffee nor team has an "x".

Generic formula

=XOR(criteria1,criteria2)

Explanation 

In this example, the XOR function contains two expressions, one to test for an "x" in column C, and one to test for an "x" in column D.

C5="x" // TRUE if coffee is "x"
D5="x" // TRUE if tea is "x"

With two logical criteria, XOR has a particular behavior, summarized in the table below:

Coffee Tea Result
TRUE FALSE TRUE
FALSE TRUE TRUE
TRUE TRUE FALSE
FALSE FALSE FALSE

At each row in column E, XOR evaluates values in columns C and D and returns a TRUE or FALSE result. This behavior is sometimes referred to as "exclusive OR", meaning only one result can be true.

Note: with more than two criteria, XOR behavior changes, as explained on the XOR function page.

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.