SheetSense

SWITCH to map codes to labels

SWITCH compares one value to a list of cases in order and returns the result for the first case that is equal, or a default if none match.

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

The sheet: Column A holds one-letter region codes. You want the full region name next to each.

How it works, step by step

  1. 01A2 is the value being tested.
  2. 02Each pair after it is a case and its result: N gives North, S gives South, and so on.
  3. 03The last argument has no partner, so it is the default returned when nothing matches.

The same logic in four tools

Excel
=SWITCH(A2,"N","North","S","South","E","East","W","West","Unknown")
Google Sheets
=SWITCH(A2,"N","North","S","South","E","East","W","West","Unknown")
SQL
SELECT code,
       CASE code
         WHEN 'N' THEN 'North'
         WHEN 'S' THEN 'South'
         WHEN 'E' THEN 'East'
         WHEN 'W' THEN 'West'
         ELSE 'Unknown'
       END AS region
FROM stores;
Python (pandas)
mapping = {"N": "North", "S": "South", "E": "East", "W": "West"}
df["region"] = df["code"].map(mapping).fillna("Unknown")

Mistakes that break it

  • !SWITCH only tests for equality. For ranges such as score bands use IFS.
  • !Excel needs 2019 or Microsoft 365. Older versions need nested IF.
  • !Without a default value, an unmatched code returns #N/A.
  • !A code with a trailing space will not match. Clean it with TRIM first.

Common questions

Is SWITCH better than VLOOKUP for this?
For a handful of fixed codes SWITCH is quicker. For a long or changing list, keep the mapping in a table and use a lookup.
Is SWITCH case sensitive?
No, in Excel and Google Sheets "n" matches "N".

Got a version of this with your own ranges?

Paste it into the free translator and get the same breakdown for your exact formula. To run it against a real file, upload the CSV and describe the formula you need in a sentence.