Documentation/Calc Functions/SWITCH

Function name:
SWITCH

Category:
Logical

Summary:
Compares a supplied expression against a set of expected values and returns the result corresponding to the first matching value. If there is no match, a default result is returned if supplied.

Syntax:
SWITCH(Expression; Value 1; Result 1 [; Value 2; Result 2][; ... ; [Value 127; Result 127][; Default Result]])

Returns:
Returns a value whose type depends on the nature of the selected result argument.

Arguments:
Expression is an expression, or a reference to a cell containing an expression, that evaluates to a value that is to be compared to the Value n arguments.

Value 1, Value 2, ..., Value 127 are expressions, or references to cells containing expressions, that evaluate to values to be compared against Expression. Each Value n argument must have a corresponding Result n argument.

Result 1, Result 2, ..., Result 127 are expressions, or references to cells containing expressions, that give the results to be returned when a corresponding Value n argument matches Expression.

Default Result is an expression, or a reference to a cell containing an expression, that gives the result to be returned if no Value n argument matches Expression.


 * If you choose not to specify a default result, 127 value / result pairs may be entered as parameters. If you choose to include a default result at the end of the list of parameters, then only 126 value / result pairs may be entered.
 * If no Value n argument matches Expression and no Default Result argument is supplied, SWITCH reports a #N/A error.

Details specific to the SWITCH function

 * The SWITCH function can often be used as an easier-to-read and more efficient alternative to nested IF functions.

Basic example
Suppose the cell A1 contains an arbitrary date. Then the following formula will extract the month value from that date as an integer and then convert the number to a string giving a three-character abbreviation of the month.


 * If A1 contains the date 1965-02-02, then the formula returns "Feb".
 * If A1 contains the number 44528, then the formula returns "Nov". 44528 is the date-time serial number that corresponds to 2021-11-28, assuming the default day 0 of 1899-12-30 is applied.

Greater use of cell references
Suppose a local shop sells small, medium, large, and extra-large pizzas. The prices of these pizzas are $9.49, $10.99, $12.99, and 14.98 respectively.

Four customers purchase pizzas – Aidan (extra-large x 4), Jimmy (large x 3), Lara (medium x 2), and Natalie (small x 1).

How much does each customer pay in total?

Arrange the known data in a spreadsheet, as in the following table. The SWITCH function can be used in cells D9:D12 to calculate the amount owed by each person.


 * Cell D9 contains the formula.
 * Cell D10 contains the formula.
 * Cells D11 and D12 follow the same pattern.

More complex conditions
Consider the data in the following table.

The data in cells A2:C7 define the grading bands for a particular exam, in terms of the low and high exam scores for each band (as percentages).

Seven students (cells A10:16) sat the exam and their scores appear in cells B10:B16. Unfortunately, two obvious data entry errors have occurred in the exam scores!

The SWITCH function can be used in cells C10:C16 to determine in which grade band each person’s exam score lies.


 * The following formula is entered into cell C10:


 * A similar formula is entered in cells C11:C16 but with references to B10 replaced by references to B11, B12, B13, B14, B15, and B16 respectively.

Note how the data entry errors in the exam scores for Angelina and Winston manifest themselves with a grade band of "Invalid score". This is achieved by setting the Default Result argument to the string "Invalid score".

Related LibreOffice functions:
AND

Special:MyLanguage/Documentation/Calc_Functions/FALSE

IF

IFERROR

IFNA

IFS

NOT

OR

Special:MyLanguage/Documentation/Calc_Functions/TRUE

XOR

ODF standard:
None.

Equivalent Excel functions:
SWITCH