SWITCH and XOR functions in Excel
Last updated
SWITCH compares one value with a list and returns the result paired with the first match: =SWITCH(B2,1,"High",2,"Medium",3,"Low") turns a priority code into a label. XOR returns TRUE when exactly one of two conditions is true: =XOR(B2="Yes",C2="Yes") accepts pickup or delivery, but not both.
SWITCH and XOR syntax
=SWITCH(expression, value1, result1, [default_or_value2, result2], …) =XOR(logical1, [logical2], …)
- expression
- The value SWITCH looks at, usually a cell such as
B2, or a formula such asLEFT(A2,2). - value1, result1
- A value to compare with the expression and the result to return when they match. Add more pairs, up to 126.
- default
- Optional. A last argument without a partner, returned when nothing matches. Without it, SWITCH returns #N/A.
- logical1, logical2, …
- The conditions XOR combines, such as
B2="Yes". With two, XOR is TRUE when exactly one is TRUE.
Practice SWITCH and XOR here
Type your formula straight into the sheet. It’s checked like real Excel, and you can write it with commas or semicolons.
Turn priority codes into labels
The help desk stores priorities as codes. In C2:C6, use SWITCH to show High for 1, Medium for 2 and Low for 3, based on the code in column B.
| Ticket | Priority code | Priority |
| T-301 | 2 | |
| T-302 | 1 | |
| T-303 | 3 | |
| T-304 | 1 | |
| T-305 | 2 |
How to use SWITCH and XOR, step by step
- For SWITCH, type
=SWITCH(and the cell to read, then a comma. - Add each value and its result in pairs, e.g.
1,"High",2,"Medium". Text goes in double quotes. - End with a default value on its own, such as
"Other", so unknown codes don’t show #N/A. - For XOR, write the two conditions as you would for OR,
=XOR(B2="Yes",C2="Yes"), and use it alone or as the test of IF.
SWITCH and XOR examples
Codes to labels
=SWITCH(B2,1,"High",2,"Medium",3,"Low")
Returns Medium when B2 is 2. Each code sits right next to its label, which is easier to read than two nested IFs.
A default for everything else
=SWITCH(B2,"A",5,"B",8,"C",12,20)
Zone A pays 5, B pays 8, C pays 12, and any other zone pays 20. The 20 has no partner, so it is the default.
SWITCH on part of a code
=SWITCH(LEFT(A2,2),"KB","Keyboard","MS","Mouse","Other")
Reads the first two letters of KB-102 and returns Keyboard. Any other prefix gives Other.
Exactly one of two
=IF(XOR(B2>0,C2>0),"OK","Fix")
OK when an employee has an hourly rate or a monthly salary. Both amounts, or none, give Fix.
Common SWITCH and XOR mistakes and how to fix them
| Result | Cause | Fix |
|---|---|---|
| #N/A | SWITCH found no matching value and has no default. | Add a default as the last argument, e.g. =SWITCH(B2,1,"High",2,"Medium","Other"). |
| Default for every row | The values don’t match what the cell really holds: the number 1 is not the text "1", and "A " with a space is not "A". | Compare like with like: drop the quotes for numbers, and clean the cell with TRIM if it has stray spaces. |
| XOR is TRUE with three conditions true | With more than two conditions, XOR is TRUE when an odd number of them are TRUE, not when exactly one is. | For “exactly one of three”, count instead: =(B2="Yes")+(C2="Yes")+(D2="Yes")=1. |
| #NAME? | SWITCH needs Excel 2019 or Microsoft 365, and XOR Excel 2013 or later. Older versions don’t know them. | In an older version, use nested IFs for SWITCH, and =(B2="Yes")<>(C2="Yes") for XOR. |
SWITCH vs IFS, and XOR vs OR
SWITCH compares one value with exact matches, so it suits codes and labels. When each case is a separate condition, such as a score of at least 90, IFS or nested IFs fit better. Past a dozen codes, a small table with VLOOKUP or XLOOKUP is easier to update than a long formula. XOR completes AND and OR: AND needs all conditions, OR needs at least one, and XOR with two conditions needs exactly one.
Frequently asked questions
What is the difference between SWITCH and IFS?
SWITCH reads one value and looks for an exact match in a list. IFS checks separate conditions in order, so it handles ranges such as B2>=90.
Can SWITCH test ranges of values?
Yes, with TRUE as the expression: =SWITCH(TRUE,B2>=90,"A",B2>=75,"B","C") returns the result of the first condition that is TRUE.
What does XOR return with more than two conditions?
TRUE when an odd number of them are TRUE: one, three, five… With three TRUE conditions, XOR still returns TRUE.
How many values can SWITCH compare?
Up to 126 value and result pairs. Long before that, a lookup table is easier to read and update.
Keep practising
All 5 SWITCH and XOR exercises, with explanations and progress tracking.
Open the SWITCH and XOR practice set