About this exercise
A 200-row dataset on one sheet, one answer cell, about 8 minutes. Graded in your browser: the value and the formula behind it, with feedback that names the fix.
How to solve it
- F2
=SWITCH(B2,"N","North","S","South","E","East","W","West","C","Central","Unknown")
SWITCH takes one expression — the code in B2 — and a list of value/result pairs: when the code equals "N" it returns "North", when it equals "S" it returns "South", and so on. An odd final argument is the default, returned when nothing matched, which is how the blank and mistyped codes become Unknown instead of an error. IFS does the same with explicit tests (B2="N", B2="S", …) and a TRUE,"Unknown" pair at the end; both read far better than four nested IFs.
Pitfall 1: forgetting the default — SWITCH without it returns #N/A for the unmapped codes, and IFS without the TRUE branch does the same, which then poisons every COUNTIF and pivot built on the column. Pitfall 2: SWITCH only tests equality against one expression; for ranges (>=) you need IFS or a lookup. Comparisons are case-insensitive ("n" matches "N") but a trailing space in the code is a different value — TRIM(B2) when the export is dirty. Pitfall 3: a mapping typed into a formula is hidden from the people who maintain the codes; once the list has more than a handful of entries, put it in a two-column table and use XLOOKUP(B2,codes,names,"Unknown") or VLOOKUP with IFERROR. Version notes: SWITCH and IFS exist in Excel 2019, 365 and the web app; in Excel 2016 and earlier use nested IF or CHOOSE(MATCH(B2,{"N","S","E","W","C"},0),…) wrapped in IFERROR. Performance: all variants are trivial at 200 rows; the lookup version is also the fastest to recalculate once the sheet grows to hundreds of thousands of rows.
Other ways that work
- =IFS(B2="N","North",B2="S","South",B2="E","East",B2="W","West",B2="C","Central",TRUE,"Unknown") — IFS with a catch-all TRUE branch
- =IFERROR(XLOOKUP(B2,{"N","S","E","W","C"},{"North","South","East","West","Central"}),"Unknown") — Excel 365; a lookup against inline arrays (not accepted by this task, which asks for SWITCH or IFS)
- =IF(B2="N","North",IF(B2="S","South",IF(B2="E","East",IF(B2="W","West",IF(B2="C","Central","Unknown"))))) — the nested-IF version this exercise replaces
Common mistakes
Leaving out the default value: =SWITCH(B2,"N","North",…,"C","Central") returns #N/A on the 6 rows with an unknown code, and the first of those is the cell the grader names.
How we grade
Any formula that produces the right result is accepted, unless the task names a function: then another function earns half credit. We compare values with a small tolerance and ignore case and spaces in text. A correct number typed by hand instead of a formula earns half credit. Multi-cell tasks earn full credit at 100% of cells, half credit from 50%. Where it matters, we also change an input cell on a copy of your sheet and check that your formula follows.
Answers computed with Excel 365 rules and checked in our grading engine on November 16, 2026. Author: GridBelt team.