Excel 2019 or laterExamples need Excel 2021 or laterLogicalUnder review

SWITCH

SCHAKELEN in Dutch Excel

Compares one value with a list of possible values and returns the result that belongs to the first exact match, with an optional fallback.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

ABCD
1Entry no.Journal codeAmountJournal
225-0412VK€12,100.00€ 12.100,00Sales journal=SWITCH(B2, "BK", "Bank journal", "VK", "Sales journal", "IK", "Purchase journal")=SWITCH(B2; "BK"; "Bank journal"; "VK"; "Sales journal"; "IK"; "Purchase journal")
325-0413IK€4,235.50€ 4.235,50
425-0414BK€8,750.00€ 8.750,00
525-0415MEM€3,025.00€ 3.025,00

Result: Sales journal

=SWITCH(B2, "BK", "Bank journal", "VK", "Sales journal", "IK", "Purchase journal")
=SWITCH(B2; "BK"; "Bank journal"; "VK"; "Sales journal"; "IK"; "Purchase journal")

What SWITCH does and when to use it

What it does: it compares one value with a list of codes and returns the result that belongs to the first match, for example the journal name behind an entry code.

When to use it: when every code has its own name, rate or category: journal codes, VAT codes, risk ratings. It replaces nested IF functions that test the same cell again and again. For ranges and comparisons (more than 1,000), use IFS.

Three main gains:

  • Tested value written once: the expression is named a single time instead of in every IF.
  • Built-in fallback: a last argument without a partner is the default for unknown codes.
  • Reads as a list: code and result sit side by side in pairs.

How Excel evaluates it

Excel works out expression once. It then reads the arguments after it in pairs, from left to right:

  • it compares expression with value1, and when they are equal it returns result1 and stops
  • when they differ, it moves on to value2, and so on
  • only the first match counts, so a repeated value is never reached
  • when no value matches, Excel returns default if you gave one (the last argument, without a partner) and #N/A if you did not

Watch out: A missing result leaves the last value without a partner, and Excel reads it as the default: every code without a match then shows that value instead of an error.

Mental model

Think of a sorting table with a row of labeled bins. A parcel comes in, you read its code, check the bins from left to right and drop it into the first bin with that code. With no such bin, it goes into the overflow bin if you have one, and otherwise it comes back rejected.

Terms used on this page: spill, argument, range, cell

Syntax

Official SWITCH documentation
SWITCH(expression, value1, result1, [value2, result2, result2], ..., [default])
SWITCH(expression; value1; result1; [value2, result2; result2]; ...; [default])
ArgumentTypeMeaning
expressionRequiredThe value to compare; it is worked out once.
value1RequiredThe first value to compare with the expression.
result1RequiredWhat the function returns when value1 matches.
value2, result2OptionalFurther pairs of a value and its result.
defaultOptionalWhat to return when nothing matches. Always the last argument.

Return value:The result paired with the first value equal to expression, else default, else #N/A

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 5 sheets, 17 KB)

Beginner

Name the journal behind each entry code

You review the general ledger extract of a client. Each journal entry carries a short journal code, but your working paper needs the name of the journal. You want a column that turns BK, VK and IK into Bank journal, Sales journal and Purchase journal.

ABCDE
1Entry no.Journal codeAmountJournal
225-0412VK€12,100.00€ 12.100,00Sales journal=SWITCH(B2, "BK", "Bank journal", "VK", "Sales journal", "IK", "Purchase journal")=SWITCH(B2; "BK"; "Bank journal"; "VK"; "Sales journal"; "IK"; "Purchase journal")
325-0413IK€4,235.50€ 4.235,50
425-0414BK€8,750.00€ 8.750,00
525-0415MEM€3,025.00€ 3.025,00
6
Formula cell (D2)Input

Formula in D2

=SWITCH(B2, "BK", "Bank journal", "VK", "Sales journal", "IK", "Purchase journal")
=SWITCH(B2; "BK"; "Bank journal"; "VK"; "Sales journal"; "IK"; "Purchase journal")

Result: Sales journal

Step by step
  1. B2 holds VK. That is the expression, and Excel works it out once.
  2. Excel compares VK with the first value, "BK". They differ, so the result that belongs to BK is skipped.
  3. The next value is "VK". It equals B2, so Excel returns the result that belongs to it: the text Sales journal.
  4. Excel stops there. The value "IK" is never looked at, which is why only the first match counts when a value appears twice.
  5. Fill the formula down to D5 and it meets MEM. MEM is not BK, VK or IK and there is no default, so D5 shows #N/A. The Intermediate example shows how to catch that.

Watch out: A memorial entry (MEM) is not in the list, so its row shows #N/A. That is correct behavior for an unknown code, but it spreads into any total that uses the column.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

SWITCH evaluates the expression once and then walks down the pairs until one value equals it. Three pairs are enough here because every code in the extract is known, and the codes are short text, so equality is exactly the test you need.

More to watch out for

  • Drop the last result by accident and "IK" becomes the default: every entry that is not BK or VK then shows the text IK, with no error to warn you.

Variations

=IF(B2="BK", "Bank journal", IF(B2="VK", "Sales journal", IF(B2="IK", "Purchase journal", NA())))
=IF(B2="BK"; "Bank journal"; IF(B2="VK"; "Sales journal"; IF(B2="IK"; "Purchase journal"; NA())))

The same result with nested IF functions, which also works in Excel 2016 and older. It repeats B2 three times and gets harder to read with each code.

Intermediate

Turn risk codes into sample sizes, with a fallback

Your audit plan lists each audit area with a risk code: H for high, M for medium, L for low. The sampling guide says to test 25 items for H, 15 for M and 8 for L.

People typed the codes by hand, some in lowercase, and one area still holds a placeholder instead of a code. You want the sample size next to each area, and a clear message where the code is not one of the three.

ABCD
1Audit areaRisk codeSample size
2Revenuem1515=SWITCH(B2, "H", 25, "M", 15, "L", 8, "Check code")=SWITCH(B2; "H"; 25; "M"; 15; "L"; 8; "Check code")
3PayrollH
4InventoryL
5Fixed assetsTBD
6
Formula cell (C2)Input

Formula in C2

=SWITCH(B2, "H", 25, "M", 15, "L", 8, "Check code")
=SWITCH(B2; "H"; 25; "M"; 15; "L"; 8; "Check code")

Result: 1515

Step by step
  1. B2 holds m, in lowercase. That is the expression.
  2. Excel compares m with "H": no. It compares m with "M": SWITCH ignores case, so m equals M and Excel has its match.
  3. It returns the result that belongs to "M": 15. The last argument, the text Check code, has no partner and is never reached, because it is used only when nothing matches.
  4. Fill the formula down to C5. B5 holds TBD, which is not H, M or L, so no value matches and SWITCH returns the default: the text Check code. Without the last argument, C5 would show #N/A.
  5. Change B2 to H and the result becomes 25, without touching the formula.

Watch out: The default is text. SUM over column C skips it, so an area with an unknown code silently gets no sample in the total. Count the cells that show the message with COUNTIF before you rely on the sum.

More on this example: why it works, 1 more pitfall, 2 variations

Why it works

After the expression, Excel reads the arguments in pairs. Seven arguments follow the expression here, so the pairs run out one argument early, and that leftover last argument is taken as the default. The comparison ignores case, so hand-typed codes need no cleaning for capitals.

More to watch out for

  • A code with a trailing space, such as "H " from an import, does not equal H. It lands on the default, so a high-risk area is treated as unknown. TRIM the code first when the data is imported.

Variations

=SWITCH(B2, "H", 25, "M", 15, "L", 8)
=SWITCH(B2; "H"; 25; "M"; 15; "L"; 8)

No default: an unknown code gives #N/A. Prefer this when an error that stops the work is better than a text message that can be overlooked.

=SWITCH(TRIM(B2), "H", 25, "M", 15, "L", 8, "Check code")
=SWITCH(TRIM(B2); "H"; 25; "M"; 15; "L"; 8; "Check code")

The same formula with TRIM around the expression, for codes that arrive with stray spaces.

Advanced

Classify trial balance accounts by their first digit

The client's trial balance (saldibalans) comes from an export in which the account numbers are stored as text, and some have a space in front. For your analytical review you want each account assigned to a section by its first digit:

  • 1: the balance sheet
  • 4: operating costs
  • 8: revenue

Anything else, such as the suspense account 9900, must be flagged for review.

ABCD
1AccountDescriptionSection
24520RentOperating costs=SWITCH(LEFT(TRIM(A2), 1), "1", "Balance sheet", "4", "Operating costs", "8", "Revenue", "To review")=SWITCH(LEFT(TRIM(A2); 1); "1"; "Balance sheet"; "4"; "Operating costs"; "8"; "Revenue"; "To review")
31710Wage tax payable
48010Product sales
54100Wages and salaries
69900Suspense account
7
Formula cell (C2)InputMarks: extra spaceNote: The account numbers are text. A2 starts with a space, which the grid draws as a small mark.

Formula in C2

=SWITCH(LEFT(TRIM(A2), 1), "1", "Balance sheet", "4", "Operating costs", "8", "Revenue", "To review")
=SWITCH(LEFT(TRIM(A2); 1); "1"; "Balance sheet"; "4"; "Operating costs"; "8"; "Revenue"; "To review")

Result: Operating costs

Step by step
  1. Excel works from the inside out. A2 holds " 4520", with a space in front.
  2. TRIM removes the leading space and returns "4520".
  3. LEFT takes the first character of "4520" and returns "4". It is text, not the number 4, because LEFT always returns text.
  4. SWITCH now has "4" as its expression. It compares with "1": no. It compares with "4": yes, so it returns the text Operating costs.
  5. For 9900 in A6 the first digit is "9". It equals none of "1", "4" and "8", so SWITCH returns the unpaired last argument: To review.
  6. Without TRIM, LEFT would return a space for A2. A space matches nothing, so the account would land silently on To review.

Watch out: Write 4 without quotes and no account ever matches, because the text "4" is not the number 4. Every row falls to the default and shows To review. There is no error, only a column that is wrong everywhere.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

TRIM and LEFT turn a messy account number into a clean one-character key, and SWITCH maps that key to a section. The keys in the formula are written in quotes because LEFT returns text, and SWITCH treats "4" and 4 as different values.

More to watch out for

  • TRIM does not remove a non-breaking space (CHAR(160)), which exports often contain. The first character is then not a digit and the account falls to the default. Check with LEN on a few rows that look right.

Variations

=SWITCH(VALUE(LEFT(TRIM(A2), 1)), 1, "Balance sheet", 4, "Operating costs", 8, "Revenue", "To review")
=SWITCH(VALUE(LEFT(TRIM(A2); 1)); 1; "Balance sheet"; 4; "Operating costs"; 8; "Revenue"; "To review")

Convert the first character to a number with VALUE and write the keys without quotes. Choose this when the keys are shared with other numeric columns.

MasterExcel 2021 or later

Recalculate the VAT of every line in one formula

You test the VAT return of a client. The columns are:

  • B: the VAT code of each invoice line (H for 21%, L for 9%, N for 0%)
  • C: the net amount
  • D: the VAT as booked

One cell should recalculate the VAT for all lines from the code and the net amount, compare the total with the booked total and show the difference. A code that is not in the list must stop the calculation instead of counting as 0%.

ABCDEFG
1LineVAT codeNet amountBooked VATDifference
211H€1,000.00€ 1.000,00€210.00€ 210,00€1.00€ 1,00=LET( code, B2:B6, net, C2:C6, booked, D2:D6, rate, SWITCH(code, "H", 0.21, "L", 0.09, "N", 0, NA()), ROUND(SUM(booked) - SUM(net * rate), 2) )=LET( code; B2:B6; net; C2:C6; booked; D2:D6; rate; SWITCH(code; "H"; 0,21; "L"; 0,09; "N"; 0; NA()); ROUND(SUM(booked) - SUM(net * rate); 2) )
322L€2,500.00€ 2.500,00€225.00€ 225,00
433H€840.00€ 840,00€176.40€ 176,40
544N€1,200.00€ 1.200,00€0.00€ 0,00
655L€600.00€ 600,00€55.00€ 55,00
7
Formula cell (F2)Input

Formula in F2

=LET(
  code, B2:B6,
  net, C2:C6,
  booked, D2:D6,
  rate, SWITCH(code, "H", 0.21, "L", 0.09, "N", 0, NA()),
  ROUND(SUM(booked) - SUM(net * rate), 2)
)
=LET(
  code; B2:B6;
  net; C2:C6;
  booked; D2:D6;
  rate; SWITCH(code; "H"; 0,21; "L"; 0,09; "N"; 0; NA());
  ROUND(SUM(booked) - SUM(net * rate); 2)
)

Result: €1.00€ 1,00

Step by step
  1. LET names the three ranges and does no calculation yet: code is B2:B6 (H, L, H, N, L), net is C2:C6 and booked is D2:D6.
  2. For rate, SWITCH receives the whole range as its expression. Microsoft's help page does not describe a range here; in Excel 2021 and Microsoft 365 the function is expected to work through the five codes one by one and return five rates: 0.21, 0.09, 0.21, 0 and 0.09. The default NA() is not used, because every code is in the list.
  3. net * rate multiplies position by position: 1,0001.000 × 0.21 = 210; 2,5002.500 × 0.09 = 225; 840 × 0.21 = 176.4; 1,2001.200 × 0 = 0; 600 × 0.09 = 54. SUM adds them to 665.4, the VAT that should have been booked.
  4. SUM(booked) adds the booked VAT: 210 + 225 + 176.4 + 0 + 55 = 666.4.
  5. The difference is 666.4 - 665.4 = 1. ROUND to 2 decimals removes the tiny residue that binary storage of 0.09 and 0.21 can leave behind, so the cell shows 1.00. The gap sits on line 5: 55 booked against 54 expected.
  6. Change one code to X and NA() becomes the rate for that line. The whole result turns into #N/A: a loud stop, not a quiet 0%.

Watch out: The mirror-image mistake: a default of 0 instead of NA(). It looks safe, but a new or mistyped code is then taxed at 0% without any warning, and the difference you see is the VAT of that one line.

More on this example: why it works, 2 more pitfalls, 2 variations

Why it works

Here SWITCH is given a range as the expression and is expected to answer for every cell of it in Excel 2021 and later, so one call replaces a helper column. Microsoft's help page does not describe this use, so test it in your own Excel before you rely on it. Making the default NA() turns an unknown code into an error that travels through SUM and cannot be missed.

Do not hard-code rates like this when they change by period or are maintained by someone else: put them in a table and look them up with XLOOKUP, so the rate lives in one cell.

More to watch out for

  • Excel 2019 has SWITCH but no dynamic arrays. Do not expect a range as the expression to be evaluated cell by cell there, and LET is missing as well. This construction needs Excel 2021 or Microsoft 365.
  • A code with a trailing space, such as "H ", does not equal H. Here that gives #N/A, which is the point of NA(): clean the data instead of letting the line fall through.

Variations

=SWITCH(B2:B6, "H", 0.21, "L", 0.09, "N", 0, NA())
=SWITCH(B2:B6; "H"; 0,21; "L"; 0,09; "N"; 0; NA())

Only the rates, one per line, spilled down from the cell. Use it to see which line gets which rate before you add the totals.

=ROUND(SUM(D2:D6) - SUMPRODUCT(C2:C6, (B2:B6 = "H") * 0.21 + (B2:B6 = "L") * 0.09), 2)
=ROUND(SUM(D2:D6) - SUMPRODUCT(C2:C6; (B2:B6 = "H") * 0,21 + (B2:B6 = "L") * 0,09); 2)

Works in every version, but any other code, including a mistyped one, counts as 0%. Choose it only when the codes are controlled elsewhere.

Common mistakes

SymptomCauseFix
#N/A or wrong result with >1000A comparison such as >1000 is typed as a value, and the cell shows #N/A or the wrong result.SWITCH tests equality only. Use IFS for conditions, or look the band up in a table with XLOOKUP.
#N/A for a code not in the listThere is no default, and a code that is not in the list gives #N/A.Add a last argument without a partner for the fallback, or leave the error in place when you want unknown codes to stop the work.
Equal-looking code falls to the defaultThe code looks equal but is not: a trailing space, or a number stored as text, so the value falls through to the default.Clean the expression with TRIM, or convert it with VALUE, so that it has the same type and characters as the values in the formula.
Unmatched codes suddenly show a valueA result is left out, and every unmatched code suddenly shows a value instead of #N/A.Count the arguments after the expression: an even number means complete pairs, an odd number means the last one is the default.