Excel track · Exercise 26 of 86 Paid
SWITCH: match a list of values
Translate a short fixed list of codes into labels in one formula.
Advanced
Item codes with a numeric tier column
Read-only data · screen 1 of 2 · answer in D2
In D2, translate the code in A2 into the item it names, with Unknown as the fallback.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Code | Tier | Item | Answer |
| 2 | W-01 | 2 | Widget | |
| 3 | G-02 | 3 | Gadget | |
| 4 | S-03 | 1 | Screw | |
| 5 | Lookup |
In D3, turn the tier number in B3 into its name: 1 Bronze, 2 Silver, 3 Gold.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Code | Tier | Item | Answer |
| 2 | W-01 | 2 | Widget | |
| 3 | G-02 | 3 | Gadget | |
| 4 | S-03 | 1 | Screw | |
| 5 | Tier names |
Reference answer
What it does
SWITCH takes one value, then pairs of a match and the result to give back. It compares the value with each match in turn and returns the result beside the first exact one. The pairs hold plain values rather than comparisons, so a text "3" and a number 3 are not the same match. That is the usual way the function falls through to its default.
A2 holds W-01, so the first pair in =SWITCH(A2,"W-01","Widget","G-02","Gadget","Unknown") returns Widget. The second screen compares tier 3 in B3 with 1, 2 and 3, and the third pair gives Gold. Quote those matches and the tier misses, so the cell falls back to None.
The last argument is what comes back when no pair matches. Leave it out and an unexpected code returns #N/A in the middle of a column of labels.
Syntax
=SWITCH(expression, value1, result1, value2, result2, ..., default)Worked example
On Item codes with a numeric tier column, =SWITCH(A2,"W-01","Widget","G-02","Gadget","Unknown") returns Widget.
=SWITCH(A2,"W-01","Widget","G-02","Gadget","Unknown")Common errors
| Error | What it means and how to fix it |
|---|---|
#N/A | No pair matched and no default was supplied, which is what a text match against a number produces. |
#VALUE! | A text result was used in arithmetic, or a pair is missing its result. |
#NAME? | SWITCH is absent from the version of Excel in use, or the name is mistyped. |
At work
Translating status codes and cost centres into labels without maintaining a lookup range.