Skip to content
Tutorials Center
My learning

Topics

Lesson 18 of 93 ยท Logical and Conditional Formulas

IFS, SWITCH and Choosing Between Many Cases

Choose between many outcomes in Excel with IFS, SWITCH and CHOOSE, set default values, and know when a lookup table beats a long formula.

Sooner or later a formula has to pick from more than two outcomes: a grade from a score, a day name from a number, a shipping price from a region code. You can stack nested IFs, but Excel offers three functions built for the job: IFS, SWITCH and CHOOSE. Each fits a slightly different kind of choice, and for long lists a lookup table is better than any of them.

IFS and SWITCH exist in Excel 2019 and Microsoft 365; CHOOSE works in every version. The formulas shown are real Excel; the live examples run on a small JavaScript stand-in engine that mimics how Excel evaluates them (it is not Excel's own code).

Pick the right chooser

IFS Ranges and conditions, first true wins.
SWITCH One value matched against exact cases.
CHOOSE Pick by position, 1 to N.
Table Many cases? Look them up.

Match the tool to the shape of the decision.

1 / 2

IFS: first true condition wins

IFS takes pairs of a test and a result:

TEXT
=IFS(test1, result1, test2, result2, ...)

It checks the tests from left to right and returns the result paired with the first true one. If none is true it returns #N/A, so the final pair is often TRUE, "default". IFS suits conditions and ranges, such as score bands:

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

SWITCH: match one value against exact cases

SWITCH evaluates one expression once and compares it with a list of values:

TEXT
=SWITCH(expression, value1, result1, value2, result2, ..., [default])

If the number of arguments after the expression is odd, the last one is the default, returned when nothing matches. With no default and no match you get #N/A. Matching is by equality, so SWITCH is the right tool for codes and labels, not for ranges:

TEXT
=SWITCH(A2, "N","North", "S","South", "E","East", "W","West", "Unknown")

There is a well-known trick for conditions: pass TRUE as the expression and use tests as the values. SWITCH(TRUE, B2<50,"Low", B2<100,"Mid", "High") then behaves like IFS, because the first test that equals TRUE wins.

CHOOSE: pick by position

CHOOSE does not test anything. It takes an index number and returns the value at that position:

TEXT
=CHOOSE(index_number, value1, value2, ...)

CHOOSE(2, "Low", "Mid", "High") returns Mid. The index must be between 1 and the number of values (a decimal is cut to a whole number); otherwise the result is #VALUE!. CHOOSE is handy when the number already exists, such as a weekday number, a quarter, or the result of MATCH.

JS SWITCH, CHOOSE and their default cases

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Reading the output line by line: A2 holds Q, which no case matches, so the second formula gives #N/A and the third gives its default Unknown. The TRUE trick returns Mid because 42 is below 100 but not below 10. CHOOSE with index 5 and only four values is #VALUE!, and the decimal index 2.9 is cut to 2.

Defaults and the "none of the above" case

Every chooser needs a plan for unexpected input:

  • IFS: end with TRUE, "default".
  • SWITCH: add one extra last argument.
  • CHOOSE: validate the index first, for example IF(AND(n>=1, n<=4), CHOOSE(n, ...), "Bad month").

Be careful with defaults that hide mistakes. A default of 0 or "Other" makes typos in the input look like valid data. Sometimes the honest result is the #N/A error, because it tells you a code is missing from your list. Handling Errors and Blanks discusses when to catch such errors and when to let them show.

When a lookup table is better

Imagine 30 product codes, each with a price. A SWITCH with 61 arguments is unreadable, and each new product means editing a formula in every cell that uses it. The better design is a small table on its own sheet, and a lookup formula:

TEXT
=VLOOKUP(A2, Prices, 2, FALSE)        // exact match on a code
=VLOOKUP(B2, Tiers, 2, TRUE)          // approximate match on a range

The table is data; anybody can edit it without touching a formula. Approximate-match lookups (the TRUE form) also replace long IFS chains for number bands, as long as the first column is sorted ascending. The details are in VLOOKUP and Its Limits and XLOOKUP and XMATCH.

JS The same decision: IFS versus a lookup table

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Both columns agree, but the second part shows the advantage: a policy change was one cell, while the IFS formula would need editing everywhere it appears. The stand-in VLOOKUP only models what is needed here; the real function has more details, covered in its own lesson.

Which should you choose?

Situation Best tool
Bands or ranges of numbers, a few cases IFS (or nested IF in old versions)
A value compared with exact codes, a few cases SWITCH
A position number to a label CHOOSE
More than about five cases, or cases that change A lookup table with VLOOKUP/XLOOKUP
Complex conditions combining several fields IF with AND/OR

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does =SWITCH(A1, 1, "One", 2, "Two") return when A1 is 3?

    SWITCH returns #N/A when nothing matches and no default argument is given.
  2. 2. Which function is the best fit for turning a score of 83 into the grade band "B"?

    Score bands are ranges, not exact values. IFS (first true test wins) or a sorted lookup table handles them naturally.
  3. 3. What is the result of =CHOOSE(0, "a", "b")?

    CHOOSE counts from 1. An index of 0, or one larger than the number of values, gives #VALUE!.
  4. 4. Why is a lookup table often better than a 20-case SWITCH?

    With a table the rules are data. You edit a cell, and every formula that looks it up gives the new answer.

Summary

  • IFS(test, result, ...) returns the result of the first true test; add a final TRUE test as the default.
  • SWITCH(expr, value, result, ..., [default]) matches one value exactly; no match and no default gives #N/A. SWITCH(TRUE, ...) mimics IFS.
  • CHOOSE(index, v1, v2, ...) picks by position, counting from 1; a bad index gives #VALUE!.
  • Always plan the "none of the above" case, but avoid defaults that hide data problems.
  • For many or changing cases, store them in a table and use a lookup.

Next: SUMIF, COUNTIF, AVERAGEIF and Their IFS Versions.