SWITCH Function
Evaluates a given expression (or a value) against a list of values and will return a result corresponding to the first matching value
Evaluates a given expression (or a value) against a list of values and will return a result corresponding to the first matching value
The SWITCH function is categorized under Logical functions. The function will evaluate a given expression (or a value) against a list of values and will return a result corresponding to the first matching value. In case there is no matching value, an optional default value will be returned.
SWITCH was added in MS Excel 2016 and hence is unavailable in earlier versions. However, the function was previously available in VBA. The SWITCH function can be used instead of nested IF functions.
=SWITCH(expression, value1, result1, [default or value2, result2],…[default or value3, result3])
The SWITCH function uses the following arguments:
Type of Expression | Example |
---|---|
Cell reference | A2 |
Logical test | A2=“PASS” |
Number or text | 2 or “FAIL” |
Named range | Named_Range |
Boolean values | TRUE or FALSE |
Math expression | A2+1 |
Let’s see a few examples to understand how the SWITCH function works:
Let’s assume we are given a series of acronyms and we wish to return the value that the acronyms stand for.
The formula will be =SWITCH(A6,”DR”,”Debtors”,”CR”,”Creditors”,”JE”,”Journal Entry”,”LB”,”Ledger book”,”unknown”).
We get the result below:
If we need to use an IF nested formula, it would be a long formula. Both SWITCH function and IF function help us to specify a series of conditions. However, with SWITCH, we can define an expression and a sequence of values and results, not several conditional statements.
The best part of the SWITCH function is that we don’t need to repeat the expression several times, which sometimes happens in nested IF formulas.
Now if we needed to use the IF function, the formula would’ve been:
=IF(B5=”DR”,”Debtors”,IF(A2=”CR”,”Creditors”,IF(B5=”JE”,”Journal Entry”,IF(B5=”LB”,”Ledger Book”,”unknown”))))
Suppose we are given a few dates and we want to see if they refer to present day, tomorrow, or yesterday. Using the SWITCH, DAYS and TODAY functions together, we can achieve the desired results.
The TODAY function will return the serial number of the current date, and the DAYS function will return the number of days between two dates.
The formula to use is =SWITCH(DAYS(TODAY(),B5), 0, “Present date”, 1,”Yesterday”, -1,”Tomorrow”,”Unknown”).
In some countries, the financial year starts on a date other than January 1, so they often need to classify dates into fiscal quarters. For example, if April to March is the financial year, then April-June would be quarter 1 and so on. We can use SWITCH in such scenario:
We get the results below:
We used a formula here that would find the month number in the Dates column: if it’s month 1, it’s quarter 4; if it’s month 2, it’s quarter 4; if it’s month 3, it’s quarter 4; if it’s month 4, it’s quarter 1; and so on.
Click here to download the sample Excel file
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:
To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user. Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.
Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!