Ravindra BagaleCourses & study guides Track your progress

Excel SWITCH Function

By · Last updated:

Chala mitrano — today SWITCH. One expression, many exact matches — cleaner than a tower of IF for codes.

Fictional sample data for learning. Status codes in Kochi ERP and Seattle SaaS. Mixed ₹ and $.

Purpose

Match an expression against a list of values and return the paired result.

Return value

The matching result, or default.

Syntax

SWITCH(expression, value1, result1, [value2, result2], …, [default])

Arguments

ArgumentRequired?Description
expressionRequiredThe value to match (cell or formula).
value1, result1, …RequiredPairs of match → result.
defaultOptionalResult when nothing matches.

Version

Excel 2019 / Microsoft 365. LibreOffice: nested IF / IFS fallback.

How to use — Why → What → How

Why: Status codes map to labels. SWITCH reads clearer than nested IF for exact equals. Status codes in Kochi ERP and Seattle SaaS. Mixed ₹ and $. In Indian back offices and foreign sister teams, SWITCH shows up on approval, SLA and fee sheets every week.

What: SWITCH compares expression to each value with exact match; first hit wins; optional default at end. Read every argument in the table once before you type; each one changes the answer on purpose.

How — numbered steps:

  1. Type =SWITCH(A2,1,"New",2,"Open",3,"Closed","?") .
  2. Keep codes and labels paired.
  3. Add default.

Ravindra Bagale's Tip

Friends, after SWITCH, find one IF/logic cell in a real workbook this week that this lesson improves.

Ravindra Bagale's Tip

Friends, sleep on SWITCH; rebuild example 1 tomorrow without looking.

Ravindra Bagale's Tip

Friends, say the business rule in plain words before =SWITCH.

Ravindra Bagale's Tip

Friends, after you finish SWITCH, open yesterday’s real workbook and find one place this function would have saved ten minutes.

Ravindra Bagale's Tip

Friends, treat SWITCH like a seatbelt on logic columns — use it before a manager asks why the flag is wrong.

Download practice file: practice-switch.xlsx (includes sheet About this data, practice sheets, and Answers).

Worked examples

1) Status map

Kochi ERP.

Code₹
2500
Formula: =SWITCH(A2,1,"New",2,"Open",3,"Closed","?")

Result: Open

What it means: 2 → Open.

2) Default

Unknown.

Code
9
Formula: =SWITCH(A2,1,"New",2,"Open","?")

Result: ?

What it means: Default fires.

3) Seattle $

Plan.

Plan
Pro
Formula: =SWITCH(A2,"Free",0,"Pro",29,"Biz",99,0)

Result: 29

What it means: Text matches.

4) vs IFS

Same idea.

Code
3
Formula: =IFS(A2=1,"New",A2=2,"Open",A2=3,"Closed",TRUE,"?")

Result: Closed

What it means: IFS needs tests; SWITCH matches values.

5) WEEKDAY map

Mon label.

Date
2026-10-26
Formula: =SWITCH(WEEKDAY(A2,2),1,"Mon",2,"Tue",3,"Wed",4,"Thu",5,"Fri",6,"Sat","Sun")

Result: Mon

What it means: Expression can be a formula.

6) No default #N/A

Risk.

Code
9
Formula: =SWITCH(A2,1,"New",2,"Open")

Result: #N/A

What it means: Always add default for reports.

Common errors and fixes

#N/A — No match, no default. Fix: Add default.
#VALUE! — Bad args. Fix: Pairs + optional default.
Wrong label — Code typo. Fix: Audit the list.

Notes and tips

  • Revisit SWITCH errors after the quiz.
  • Build one example from memory.
  • Check types if results look wrong.
  • Teach a tip in Marathi or Hindi.
  • Keep currency consistent in one column (practice mixes ₹ $ £ €).
  • Drill: status code map with default from memory.
  • SWITCH vs IFS: exact value map vs ordered condition tests.
  • Fictional sample data only.
  • Print the SWITCH revision card.
  • Ask juniors to predict before Enter.
  • Track your top mistake monthly.
  • Status codes in Kochi ERP and Seattle SaaS. Mixed ₹ and $.
  • Logical tests love TRIM and UPPER when text is messy.
  • Interview tip: explain the business rule before the formula.
  • When nesting, keep each IF on its own mental line — or switch to IFS/SWITCH.
  • Classroom drill: invent three brand-new formulas from your city without the page open.
  • Share one tip with a classmate — teaching locks the idea.
  • If a result looks wrong, check blanks vs zeros before rewriting logic.
  • Print the revision card for SWITCH for one week of real worksheets.
  • When helping a junior, ask them to predict before Enter.
  • Share one tip with a classmate in Marathi or Hindi.
  • Keep currency symbols consistent inside a single report column.
  • When Excel versions differ, write a nested-IF fallback under IFS/SWITCH in a comment.
  • Logical tests love TRIM and UPPER when text is messy.
  • Interview tip: explain the business rule before the formula.

In real work

Drill: status code map with default from memory. Status codes in Kochi ERP and Seattle SaaS. Mixed ₹ and $. Classroom drill: write three brand-new SWITCH formulas on a blank sheet using names from your own city — if you can invent them without the page open, you own the skill. Then break one formula on purpose and watch the error; recovering from that break is half of interview readiness. Always verify with a known case before trusting a long chain of logical tests. Keep a personal notebook of the one mistake you make most often with this function; review it monthly.

How it compares

SWITCH vs IFS: exact value map vs ordered condition tests.

Interview corner

SWITCH vs IFS?
Exact values vs conditions.
Default?
Last argument.
Old Excel?
Nested IF.

IFS, IF, CHOOSE

Practice tasks (10)

  1. Task 1. Map codes 1/2/3.
    SWITCH.
  2. Task 2. Add default.
    Default.
  3. Task 3. Text plan prices.
    Text.
  4. Task 4. WEEKDAY labels.
    Expr.
  5. Task 5. Show #N/A without default.
    Risk.
  6. Task 6. Compare IFS.
    Interview.
  7. Task 7. Fill column.
    Fill.
  8. Task 8. Doc code list.
    Note.
  9. Task 9. Teach.
    Teach.
  10. Task 10. Bonus: CHOOSE index.
    Related.

Quick quiz

Q1. SWITCH matches:
Q2. No match without default:
Q3. vs IFS:
Q4. Expression can be:
Q5. Old Excel:

Answers: Q1=Exact values, Q2=#N/A, Q3=Values vs conditions, Q4=Cell or formula, Q5=Nested IF (check after you try).

Revision card

Standalone card (no tip or photo strip). QR opens this lesson. Download the PNG and revise offline.

Revision card: SWITCH
SWITCH revision card