How to Use the XOR Function in Excel with Examples

The XOR function in Excel, or any programming expression, implies “Exclusive OR.” 

Suppose you are planning to visit New York City and Alaska. Is it possible to visit the two places simultaneously? That, of course, is not possible. You have to visit one place first, then the other.

Now, the question is: how will you express the above circumstance in Excel? The simplest answer is the XOR function. The XOR function returns TRUE if either of two expressions results in TRUE. But the XOR returns FALSE if both expressions result in TRUE simultaneously.

This article will make you an expert on using the XOR function. 

In the later section, we also have a discussion on how XOR differs from “Inclusive OR.”

Syntax of XOR Function

=XOR(logic1, logic2, ...)

Argument of XOR Function

logic1 : A logical expression, constant, or cell reference is required to return TRUE or FALSE.

logic2 : A logical expression, constant, or cell reference to return TRUE or FALSE.  (optional)

Return of XOR Function

XOR with two Values 

Dealing with two functions using the XOR function returns TRUE only if one of two expressions results in TRUE

But if both expressions result in TRUE, the XOR function returns FALSE.

XOR with Multiple Values 

If you have to deal with more than two logical expressions, the return value will be a bit different

So, the XOR function returns TRUE only if the number of logical expressions that result in TRUE is odd.

Example #1: Application of XOR Function to Compare Text

Before starting this example we recommend keeping the following knowledge on the IF function

Syntax of the IF Function:

=IF(logic1,return1,return2)

The argument of the IF Function:

logic1: conditional expression to evaluate

return1: the text or logical expression to return if logic1 is satisfied.

return1: the text or logical expression to return if  logic1 is unsatisfied.

Let’s look at an example.

You are a member of the management committee for any football match. You have to decide whether the third match will be required for each group. Then you will count the following criteria for each group

• If Match 1 and Match 2 both win for any group, there is no need for a third match.
• Similarly, if both Match 1 and Match 2 result in Loss, a third match is not required.
• Only if one of Match 1 or 2 ends in a win or a loss but not both, is the third match necessary.

To solve the above problem in Excel, input the following formula in the output cell E3 and press Enter. 

=IF(XOR(C3:D3="Loss"), "Yes", "No")

Explanation

If the result of XOR(C3:D3=”Loss”) is TRUE. The IF function will then return “Yes”. Otherwise, the IF function returns “No”.

Let’s evaluate the formula for Group A

=IF(XOR(FALSE,FALSE), "Yes", "No")

=IF(FALSE, "Yes", "No")

=No

Example #2: AND Function Vs XOR Function

First, examine the syntax of the AND function before beginning the example.

Syntax of the AND Function

=AND(logic1, logic2, ...)

Arguments of the AND Function

logic1: Conditional or logical value to be checked 

logic2: condition or logical value to be checked (Optional)

Return Value of the AND Function

The AND function only returns TRUE when all of the inputs’ are satisfied. The AND function returns FALSE if this is not the case.

Now, start the example.

Suppose you are the managing director of any company. You want to give a bonus to your employee according to the following criteria:

• If the amount of food and beverages sold exceeds 800 and 1500, respectively, the employee is eligible for the full bonus.
• But if any one amount of food and beverages sold is less than 800 and 1500, respectively, the employee is eligible for the half bonus.

Apply the following formulas in the output cell:

For Full Bonus:

=IF(AND(C4>=800,D4>=1500),"Yes", "No")

For Half Bonus:

=IF(XOR(C4>=800,D4>=1500),"Yes", "No")

Explanation

Tom’s food and beverage sales are less than 800 and 1500, respectively. So, he is not eligible for a bonus.

Richard’s beverage sales quantity is more than 1500. So, he is eligible for a half bonus. In addition, the XOR function returns TRUE.

See the formula evaluation for Richard,

=IF(XOR(C4>=800,D4>=1500),"Yes", "No")

=IF(XOR(FALSE,TRUE),"Yes", "No")

=IF(TRUE,"Yes", "No")

=Yes

Things to Keep in Mind on XOR Function in Excel

• The XOR function is added from the Excel  2013 version or later versions.
• Up to 254 logical expressions can be added to the syntax of the XOR function.
• If any of the logical values are happened to find, the XOR function returns #VALUE! error.

XOR VS OR

If at least one of the two arguments is TRUE, the OR function returns TRUE. 

If only one of the arguments is TRUE, the XOR function returns TRUE. However, if both arguments are TRUE, the XOR function returns FALSE.

See the truth table for OR function and XOR function

Conclusion

In conclusion, the Excel XOR function in excel is a logical operation that yields FALSE if an even number of its parameters are TRUE and TRUE if an odd number of them are TRUE. It is helpful when determining if an odd or even number of logical criteria are true while evaluating many circumstances at once. The XOR function can be used in a variety of situations, including determining whether a cell value satisfies several requirements, confirming that a password or security code is correct based on many requirements, and determining whether an input is valid based on various conditions.