CHOOSE vs SWITCH in Excel

The difference in CHOOSE vs SWITCH in Excel is what you feed them. CHOOSE takes a position number and returns the value in that position of a list; SWITCH takes any value, matches it against a list of codes and returns the paired result, with an optional default. Both replace a chain of nested IFs when you turn codes into labels. This comparison is for anyone cleaning up label formulas who wants to pick the right one.

CHOOSE vs SWITCH at a glance#

CHOOSE is "give me item number n". SWITCH is "if it's this, show that".

How each function works#

CHOOSE counts along its list. =CHOOSE(2,"Standard","Express","Overnight") returns Express, the second value. The first argument is the position; everything after it is the list.

SWITCH compares. =SWITCH(D2,"P","Pending","S","Shipped","D","Delivered","Check code") checks D2 against "P", then "S", then "D", and returns the result paired with the first match. The lone value at the end, "Check code", is the default. The SWITCH function guide covers it step by step.

The same task, side by side#

The clearest comparison is one column of shipping codes, labelled both ways.

The formulas in row 2, filled down:

=CHOOSE(B2,"Standard","Express","Overnight")
=SWITCH(B2,1,"Standard",2,"Express",3,"Overnight","Check code")

For the codes 1 to 3, both give the same labels, and CHOOSE is shorter because it doesn't repeat the numbers. For 4 and 0, CHOOSE has no matching position and returns #VALUE!, while SWITCH falls back to its default, Check code.

When the codes are text#

Now suppose the system exports STD, EXP and OVN instead of numbers. CHOOSE can't use text as a position, so only SWITCH works directly:

=SWITCH(B2,"STD","Standard","EXP","Express","OVN","Overnight","Check code")

With EXP in B2, the result is Express.

When the number comes from another function#

CHOOSE shines when a function already gives you a position. WEEKDAY returns 1 for Sunday to 7 for Saturday, and MONTH returns 1 to 12:

=CHOOSE(WEEKDAY(A2),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")
=CHOOSE(MONTH(A2),"Q1","Q1","Q1","Q2","Q2","Q2","Q3","Q3","Q3","Q4","Q4","Q4")

For 4 November 2026, these return Wed and Q4. The second is a common fiscal-quarter trick: change the order of the labels when your financial year starts in another month. Get the quarter from a date shows other ways.

Picking a whole range with CHOOSE#

CHOOSE can also return a range. If B2:B5, C2:C5 and D2:D5 hold three quarters of sales and F1 holds a quarter number:

=SUM(CHOOSE(F1,B2:B5,C2:C5,D2:D5))

With 2 in F1, it adds C2:C5. Our guide to the CHOOSE function has more examples.

How CHOOSE and SWITCH compare with nested IF#

Both functions exist to replace a chain of nested IFs. Here is the shipping label written the old way:

=IF(B2=1,"Standard",IF(B2=2,"Express",IF(B2=3,"Overnight","Check code")))

It returns the same labels as the SWITCH version, but every extra code adds another IF( and another closing parenthesis, and a missing bracket breaks the whole formula. SWITCH names the value to test once, and CHOOSE doesn't need any tests at all.

So in Excel 2016 or older, CHOOSE is the tidy option for position numbers, and nested IF (or a lookup table) is the fallback for text codes. Nested IF in Excel shows how to keep long IF chains readable when you have no other choice.

When to use CHOOSE#

Use CHOOSE when the input is already a clean position number. It is the better choice when:

• Another function returns a position, such as WEEKDAY, MONTH or MATCH.
• Codes are 1, 2, 3 with no gaps, so you don't want to repeat each number as SWITCH does.
• You want to pick between ranges with a number in an input cell.
• The file must work in Excel 2016 or older. CHOOSE works everywhere.

When to use SWITCH#

Use SWITCH when you match values rather than count positions. It is the better choice when:

• Codes are text, like P, S, D or STD, EXP.
• Numbers have gaps, like 10, 20, 50, which CHOOSE would need empty slots for.
• You want a default for new or mistyped codes, so the sheet never shows an error.
• The formula should be self-explanatory. "P","Pending" reads as a code table; position lists don't.

What about ranges like scores and bands?#

Neither function is built for "90 or more is an A". CHOOSE needs a position, and SWITCH needs an exact match. For bands, IFS is clearer:

=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",TRUE,"F")

You will sometimes see =SWITCH(TRUE,B2>=90,"A",B2>=80,"B","C"). It works, because SWITCH returns the first test that equals TRUE, but IFS says the same thing more plainly. The IFS function guide and nested IF vs IFS cover band formulas. For long lists that change often, a small lookup table with XLOOKUP or VLOOKUP is easier to maintain than any formula; see VLOOKUP vs XLOOKUP.

Common mistakes with CHOOSE and SWITCH#

1. No default in SWITCH. A new code returns #N/A. Always add a final default such as "Check code".
2. Feeding text to CHOOSE. =CHOOSE("2",...) may work if the text looks like a number, but =CHOOSE("EXP",...) gives #VALUE!. Use SWITCH for text codes.
3. Codes stored as text numbers. If B2 holds '2 (text), SWITCH comparing with the number 2 finds no match. Check with ISNUMBER; see ISBLANK, ISNUMBER and ISTEXT.
4. Forgetting CHOOSE starts at 1. There is no position 0, so a code of 0 returns #VALUE!. Add 1 to zero-based codes: CHOOSE(B2+1,...).
5. Hard-coding a long list. Twenty codes in one formula are hard to edit. Move them to a table and look them up.
6. Using SWITCH in a file for Excel 2016. It shows #NAME? there. Use CHOOSE, nested IF, or a lookup table.

Our recommendation#

Use SWITCH as your default for turning codes into labels: it handles text, gaps and unknown codes, and the formula documents itself. Use CHOOSE when you already have a clean position number, especially from WEEKDAY, MONTH or MATCH, or when the file must open in older Excel.

If the list is long or changes often, use neither: put the codes in a two-column table and look them up. Text tools follow the same "pick the simplest tool for the input" rule; see LEFT vs TEXTBEFORE and Text to Columns vs TEXTSPLIT.

In short: SWITCH to match codes, CHOOSE to pick by position.

Practise it#

Turn shipping numbers into names with CHOOSE in the exercise on this page. Then take the IS functions and SWITCH lesson, where you check messy data and turn status codes into labels with a default.