|
<< Click to Display Table of Contents >> SWITCH (categorize initial value) |
Syntax:
SWITCH(Calculation, Result1, Value1 [, Result2, Value2 ...] [, DefaultValue])
Description:
This function checks an initial value and compares it to a series of defined values for the first match.
Calculation is the initial value and can be any expression.
Result1 is the first element to be compared with the initial Calculation value.
Value1 is to be returned if Result1 matches the initial value for Calculation.
Result2; Value2 (optional) is the second compared element etc.
As soon as the first match is found, its assigned value will be returned.
Note:
If no match is found, the #N/A error is returned.
However, you can instead specify a DefaultValue at the end of the formula, which will be returned if no match is found (see example 2 below).
Compatibility notes:
Microsoft Excel supports this function only in version 2019 or later. In older versions, the function is unknown.
Example 1:
If a specific number is found in a cell A1, a name defined for this purpose should be output. The following key applies: 1 stands for Lisa, 2 stands for Peter, 3 stands for Anna.
The formula SWITCH(A1, 1, "Lisa", 2, "Peter", 3, "Anna") then returns, for example
If cell A1 contains the number 1 = Lisa
If cell A1 contains the number 2 = Peter
If cell A1 contains the number 3 = Anna
If cell A1 contains anything other than 1, 2, or 3, the formula returns the result #N/A because no match was found. Instead, you can also determine which default value the formula generally returns if no match is found (see example 2).
Example 2:
You have the same initial situation as in example 1. Set a default value at the end of the formula that appears if no match is found:
SWITCH(A1, 1, "Lisa", 2, "Peter", 3, "Anna", "no match")
For example, if cell A1 contains 4, the result "no match" is returned.
See also: