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.
=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
- 01A2 is the value being tested.
- 02Each pair after it is a case and its result: N gives North, S gives South, and so on.
- 03The last argument has no partner, so it is the default returned when nothing matches.
The same logic in four tools
=SWITCH(A2,"N","North","S","South","E","East","W","West","Unknown")=SWITCH(A2,"N","North","S","South","E","East","W","West","Unknown")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;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.