Up next in 10
Master the SWITCH Function in Excel: Compare with IF & IFS for Better Results
491 views
Feb 7, 2025
Unlock the power of the SWITCH function in Excel to simplify your formulas and save time! In this tutorial, we compare the SWITCH function with nested IF and IFS functions, demonstrating how it can make your formulas cleaner, easier to understand, and more efficient. You'll learn: - How to use SWITCH for exact matches. - How to handle logical operators with SWITCH. - Real-life examples of assigning grades and applying discounts. - Why SWITCH is superior to nested IFs and IFS for multiple conditions.
View Video Transcript
0:00
welcome back to another Excel tutorial
0:02
in this video we're exploring the switch
0:04
function introduced in Excel 2019 it's a
0:08
powerful alternative to the ifs function
0:10
and eliminates the complexity of nesting
0:12
multiple if statements when dealing with
0:13
a list of values and their corresponding
0:15
results the switch function starts with
0:18
an expression as the first argument this
0:20
is the value you're testing the next
0:22
pair of arguments are value one the
0:24
first condition to compare against the
0:25
expression and result one which is the
0:28
output if value one matches the
0:30
expression you can add multiple value
0:32
result pairs to handle additional
0:34
conditions and finally you can specify a
0:36
default value as the last argument which
0:39
is returned if no matches are found
0:41
let's explore the switch function with a
0:42
simple example in this example we're
0:45
calculating discounts for customers
0:46
based on their payment method the
0:48
formula displayed on the screen is
0:50
designed to evaluate the value in cellb
0:52
2 against a list of payment method types
0:55
here's how it works if the payment
0:56
method is online the formula calculates
0:58
a 5% discount on the amount in C2 for
1:02
Visa the discount is 8% and for master
1:05
it's 10% if the payment method doesn't
1:08
match any of these the default value of
1:10
zero is returned meaning no discount is
1:13
applied once the formula is entered you
1:15
can use the fill handle to quickly copy
1:17
it down to the entire column as you can
1:19
see the discount is dynamically
1:21
calculated for each customer based on
1:23
their payment method customers using
1:25
cash as their payment method receive no
1:27
discount because they fall into the
1:29
default category
1:30
which is not eligible for any discounts
1:32
the formula displayed on the screen is a
1:34
nestive Formula that achieves the same
1:36
result as the switch function notice how
1:38
the nested if formula involves repeated
1:40
use of if parenthesis B2 equals and
1:43
requires extra closing brackets at the
1:45
end if we were to achieve the same task
1:47
using the ifs function the formula would
1:49
look like this while if eliminates the
1:51
repetitive if parenthesis syntax it
1:54
still requires B2 equals to be repeated
1:56
for each condition also since the is
1:59
function doesn't include a built-in
2:01
default value we add the condition true
2:03
comma 0er to handle unmatched cases and
2:06
avoid errors in conclusion when testing
2:08
a list of possible values the switch
2:11
function offers a more organized concise
2:14
and clutter-free formula it eliminates
2:16
unnecessary repetitions making it easier
2:18
to read write and manage effectively in
2:22
the previous example we tested for an
2:24
exact match between the expression and a
2:26
list of values which is the most common
2:28
use of the switch function but what if
2:30
we need to use logical operators such as
2:32
greater than or less than for our values
2:35
in such cases you must use true as the
2:37
expression or a function that evaluates
2:39
to true or false here's the formula
2:42
we're using in this example let's break
2:44
it down the expression here is is number
2:47
B2 which checks if the value in B2 is a
2:50
number for each value we test B2 against
2:53
the required score using logical
2:54
conditions like B2 greater than 89 the
2:57
result is a Grade B C or D if none of
3:01
the conditions match the default value
3:04
fail is returned it's also important to
3:06
note that the order of the values is
3:08
crucial the switch function stops
3:10
evaluating as soon as it finds the first
3:12
matching condition in this example we've
3:15
successfully assigned grades to student
3:17
scores making the switch function a
3:19
versatile option even for logical
3:21
comparisons next we demonstrated how the
3:23
same functionality can be achieved using
3:26
the nestive function however this
3:28
approach makes the formula more complex
3:30
and harder to understand similarly we
3:32
use the IF function to achieve the same
3:34
result by comparing the switch if and if
3:37
functions it's clear that switch offers
3:39
a cleaner and more organized solution
3:42
when working with multiple conditions
3:44
thank you for watching if you found this
3:46
tutorial helpful please share it with
3:47
others give it a like and don't forget
3:49
to subscribe to the channel hit the
3:51
notification Bell so you never miss an
3:53
update your support helps us create more
3:55
valuable content See you in the next
3:57
video