Quick Links
The IF statement is a popular logical function in Excel. The SWITCH statement is less known, but you can use it instead of the IF statement in some scenarios. Let’s explore these functions and their use cases.
What Is the IF Statement?
TheIF Statement in Excelis a function that performs a logical test to determine if the specified condition is true or false. It returns a value based on the evaluation’s result, allowing you to make decisions based on the outcomes.
The Basic IF Statement
Let’s start by examining the basic IF statement before we understand the problem with it—the problem that SWITCH tries to solve.
The syntax for the basic IF statement is:
Parameters with square brackets in the syntax are optional—you don’t need to specify them.
Thelogical_testparameter is the condition the function will check, andresult_if_trueis the result it returns if the evaluation isTRUE. Theresult_if_falseparameter is what it returns if the result isFALSE.
In the screenshot below, we want to returnPassorFailfor theGrade, depending on whether the value of the test scores in columnCis greater than or equal to50.
We will write the formula below in cellC2to start:
Once we copy the formula into the cells below, we will see the grades of each student.
Nested IF Statement
If you want to test multiple conditions simultaneously, you may place IF statements inside another.
These are called nested IF statements, and their basic syntax is:
Consider the example below, where each color in columnA(Red,Yellow, orGreen) needs to have a corresponding status in columnB(e.g.,Stop,Caution,Go, andUnknown).
First, we will enter the following formula inB2for the color inA2:
SinceA2isRed, it will returnStop, as per the formula above. Once we copy it into the other cells, every color should have a status.
As you can see, the logic can quickly get confusing the more you nest the IF statements. That’s where the SWITCH statement comes in to simplify things a little.
What Is the Switch Statement?
TheSWITCH statement in Excelis a relatively new function. It takes an expression and tests it against multiple outcomes in a more readable and structured format compared to nested IF statements.
The basic syntax of a SWITCH Statement is:
Theexpression_to_testis the value that will be evaluated against the test values (test_value1,test_value2, and so on). If one is a match, it will return the corresponding result. For instance, iftest_value1matches the expression, it will returnresult_if_true1.
You can specify thevalue_if_no_matchparameter to return a value in case no match is found.
Since the SWITCH statement can simplify a nested IF, we can use the colors example from the nested if. Here is the SWITCH version:
As you’re able to see, the formula is now more readable as a SWITCH statement and works the same as the IF version.
IF vs Switch Statement: Use Cases
When compared to the SWITCH statement, the IF statement is best used when performing complex logical tests involving multiple conditions. It also works well when the tests use differentlogical operators in Excel(e.g., >, <, =, <>, AND, and OR). Because of this, it is more flexible and can handle more dynamic situations than a SWITCH statement.
The SWITCH statement, on the other hand, works well when the logical tests involve simple equality and fixed values, considering its basic form only evaluates the equals operator. If you use a SWITCH statement for complex logical tests, it’s no different from using an IF statement.
Take this complex nested IF statement, for example:
you’re able to write it as a SWITCH statement like the one below:
Here, we have set the expression that needs testing to TRUE, which allows us to test multiple conditions using more than the equals operator. But now the logic is looking less compact and readable compared to a basic SWITCH statement.
As with any function in Excel, you can combine these functions. You can place an IF statement within a SWITCH statement and vice versa. You can also insert them into other functions, such as SUM, INDEX, MATCH, and XLOOKUP.
