Map Values with SWITCH

Excel Formulas › Logical

Excel 2019+SWITCHIFS

When you’re mapping one value to another — a code to a label, a number to a name — SWITCH is cleaner than a stack of IFs. It compares one expression against a list of cases and returns the first match.

Quick formula: to turn a status code in A2 into a label:
=SWITCH(A2, 1, "New", 2, "Open", 3, "Closed", "Unknown")
SWITCH checks A2 against each value in turn and returns its paired result; the lone last argument is the default.

Functions used (tap for the full reference guide):

SWITCHIFSCHOOSE

The example

Status codes mapped to labels.

AB
1CodeStatus
21New
33Closed
49Unknown

The formula

The label in B2:

=SWITCH(A2, 1, "New", 2, "Open", 3, "Closed", "Unknown") // 3 → Closed; 9 → Unknown (default)

How it works

SWITCH reads as value / result pairs:

  1. The first argument is the expression to test — A2.
  2. Then come pairs: a value to match and the result to return. 1, "New" means “if A2 is 1, return New.”
  3. SWITCH returns the first matching result and stops.
  4. A final lone argument (no pair) is the default when nothing matches — here, “Unknown.”

SWITCH vs IFS. Use SWITCH when you’re comparing one thing to many exact values. Use IFS when each branch is a different condition (ranges, comparisons). For a position-based pick (1st, 2nd, 3rd item), CHOOSE is simplest.

Try it: interactive demo

Live demo

Pick a code; see the mapped label.

Status:

Variations

Text codes too

SWITCH works on text values:

=SWITCH(A2, "N", "New", "O", "Open", "Closed")

Legacy: nested IF or lookup

Pre-2019, use a small lookup table with XLOOKUP/VLOOKUP, or nested IF.

=IF(A2=1,"New",IF(A2=2,"Open",IF(A2=3,"Closed","Unknown")))

Pick by position with CHOOSE

When codes are 1,2,3 in order:

=CHOOSE(A2, "New", "Open", "Closed")

Pitfalls & errors

SWITCH needs Excel 2019+. Older versions show #NAME? — use nested IF or a lookup table.

SWITCH matches exact values, not ranges. For “greater than” logic use IFS; SWITCH can fake ranges only with a TRUE trick.

No default = #N/A. Without the final lone argument, an unmatched value returns #N/A. Always add a default.

Practice workbook

📊
Download the free Map Values with SWITCH practice workbook
Status codes with SWITCH (result shown), the IFS and CHOOSE alternatives, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I use the SWITCH function in Excel?
=SWITCH(expression, value1, result1, value2, result2, ..., default). It compares the expression to each value and returns the first match, or the default if none match. Requires Excel 2019+.
What's the difference between SWITCH and IFS?
SWITCH compares one expression to several exact values. IFS evaluates a series of separate conditions (like ranges or comparisons). Use SWITCH for value mapping, IFS for condition logic.
How do I do SWITCH in older Excel?
Use nested IF, or better, a small lookup table with VLOOKUP or XLOOKUP, which is easy to maintain and works in every version.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Convert scores to grades · IF with AND / OR · Lookup with multiple criteria

Function references: SWITCH · IFS · CHOOSE