SWITCH Function in Excel: Map Codes to Labels Without a Lookup Table

Keep the code numbers in the formula and the labels beside them. Writing the labels first and the codes second is the most common way to get a column of words that all look plausible and are all shifted by one position.

SWITCH reads cell references, not text, in the case positions. A case written as "10" with quotes is a text comparison, and section 3 covers what that does to a numeric column.

The default sits inside the same formula as the mapping, so the mapping travels with the workbook. Send the sheet to a colleague without the lookup tab and column D still works.

Write the default on its own line. Excel keeps line breaks inserted with Alt+Enter inside the formula bar, and a default alone at the end is harder to bury under the next pair someone appends.

SWITCH accepts up to 126 pairs, which is the 254-argument ceiling halved, so parity will not stop you. Readability will. At six pairs a reviewer starts counting arguments instead of checking the logic.

The default can be a function call, and =NA() is a legitimate one. It forces #N/A for every unmapped code rather than printing a label that looks like a real status.

Text comparison ignores case the way the equals sign does, so a status exported as canceled matches a case written Canceled. SWITCH has no exact-case mode; when case matters, EXACT inside SUMPRODUCT is the tool.

Codes that arrive as text are the most common cause of a full column of defaults. COUNT against COUNTA on the code column shows it in one cell: 11 against 12 means one code is not a number.

Google Sheets behaves the same on all three counts: equality only, no wildcard expansion, and text kept apart from numbers. Its own documentation says the cases are matched exactly.

IFNA wrapped around the SUM returns 0 instead of #N/A. A dashboard reading $0.00 looks like a quiet month; a dashboard reading #N/A gets fixed the same day.

A blank code cell matches none of the pairs, so it lands on the default. With a numeric default of 0 that blank row contributes nothing to a SUM, which is usually right and occasionally is not.

Keep the numeric SWITCH in its own column rather than nesting it inside the amount formula. When a code goes missing, the error appears in one narrow place instead of taking the revenue column down with it.

Array constants cannot hold function calls, so {10,20,30,40,90} has to be literal numbers. Dates would have to go in as their serial numbers, typed out by hand.

A table is the only version where the person who owns the codes can edit the mapping. The trade-off is a second place to look when a label comes out wrong, and a table that drifts from the export it describes.

Excel 2016 and older return #NAME? for SWITCH and IFS both, and the cell shows _xlfn.SWITCH( in the formula bar when the file was written in a newer version. Sheets has no equivalent version problem.