SWITCH
Compare one expression to several values and return the matching result.
Syntax
SWITCH(expression, value1, result1, [value2, result2], ..., [default])| Argument | Required | Description |
|---|---|---|
expression | Required | The value to match. |
value1 | Required | A value to compare against. |
result1 | Required | What to return on a match. |
default | Optional | An odd final argument is returned when nothing matches. |
Examples
Every example below was executed against VisiGrid engine 0.35.0 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=SWITCH(2,1,"one",2,"two","other") | two | |
=SWITCH(9,1,"one","other") | other | The trailing odd argument is the default. |
Equality only
SWITCH compares for equality. It cannot express “greater than”, which is what
IFS is for. Use SWITCH when you are mapping known codes
to labels and IFS when you are testing ranges.
When a lookup table is better
Past about five options, a two-column table and
VLOOKUP or XLOOKUP beats a
long SWITCH — the mapping becomes data you can see and edit rather than
formula text, and adding a case stops meaning editing every copy of the formula.
Excel compatibility
Matches Excel.