Every Excel Pro must understand IF and SWITCH: Here’s how they compare

The IF statement is a popular logical function in Excel. The SWITCH statement is less well known, but in some scenarios you can use it instead of the IF statement. Let’s explore these features and their use cases.

What is an IF statement?

The IF statement in Excel is a function that performs a logical test to determine whether a specified condition is true or false. It returns a value based on the result of the evaluation, allowing you to make decisions based on the results.

Basic IF command

Let’s start by exploring the basic IF statement before understanding the problem with it – the problem that SWITCH is trying to solve.

The basic IF statement syntax is:

IF (logical_test, result_if_true, [result_if_false])
 

The logical_test parameter is the condition that the function will check, a result_if_true is the result it returns if the evaluation is FAITHFUL. The result_if_false parameter is what it returns if the result FALSE.

In the screenshot below, we want to go back Pass or Fail for Degreedepending on whether the test score value is in the column C is greater than or equal to 50.

We will write the formula below in the cell C2 start:

=IF(C2 >= 50, "Pass", "Fail")
 

Once we copy the formula into the cells below, we will see each student’s grades.

Nested IF statement

If you want to test multiple conditions at the same time, you can put the IF statements in another.

These are called nested IF statements and their basic syntax is:

=IF(logical_test1, result_if_true1, IF(logical_test2, result_if_true2, result_if_false2))
 

Consider the example below where each color is in a column AND (Red, Yellowor Green) must have a corresponding status in the column B (e.g, BUS STOP, Attention, Goand Unknown).

First, we enter the following formula B2 for color v A2:

=IF(A2 = "Red", "Stop", IF(A2 = "Yellow", "Caution", IF(A2 = "Green", "Go", "Unknown")))
 

From A2 is Redwill return BUS STOPaccording to the above formula. Once we copy it to the other cells, each color should have its own state.

As you can see, the logic can get confusing quickly the more you nest IF statements. This is where the SWITCH command comes in to make things a little easier.

What is a Switch command?

The SWITCH command in Excel is a relatively new feature. Compared to nested IF statements, IF takes an expression and tests it against multiple results in a more readable and structured format.

The basic syntax of the SWITCH command is:

SWITCH (expression_to_test, test_value1, result_if_true1, [test_value2, result_if_true2,…], [value_if_no_match])
 

The expression_to_test is the value that will be evaluated against the test values ​​(test_value1, test_value2and so on). If one matches, it returns the corresponding result. For example, if test_value1 matches the expression, will be returned result_if_true1.

You can specify value_if_no_match a parameter that returns a value if no match is found.

Since the SWITCH statement can simplify the nested IF, we can use the colors example from the nested if. Here is the SWITCH version:

=SWITCH(A2, "Red", "Stop", "Yellow", "Caution", "Green", "Go", "Unknown")
 

As you can see, the formula is now more readable as a SWITCH statement and works just like the IF version.

IF vs Switch Statement: Use Cases

Compared to the SWITCH statement, the IF statement is best used when performing complex logic tests involving multiple conditions. It also works well when the tests use different logical operators in Excel (eg >, <, =, <>, AND and OR). For this reason, it is more flexible and can handle more dynamic situations than the SWITCH command.

The SWITCH statement, on the other hand, works well when logical tests involve simple equality and fixed values, given its basic form it only evaluates the equals operator. If you’re using the SWITCH statement for complex logic tests, it’s no different than using the IF statement.

Take this complex nested IF statement, for example:

=IF(A1 >= 90, "Grade A", IF(A1 >= 75, "Grade B", "Grade C"))
 

You can write this as a SWITCH statement like the one below:

=SWITCH(TRUE, A1 >= 90, "Grade A", A1 >= 75, "Grade B", A1 < 75, "Grade C")
 

Here we set the expression that needs testing to TRUE, which allows us to test multiple conditions using the greater than equals operator. But now the logic looks less compact and readable compared to the basic SWITCH statement.