Course / Clean & Check Data

Mapping codes with CHOOSE and SWITCH

Turn priority numbers, channel codes and dates into readable labels without a lookup table. Intermediate · 5 min · CHOOSE, SWITCH, WEEKDAY

Systems love codes: priority 1 to 4, channel EM or PH. Codes are compact to store but slow to read, and a report full of them sends everyone hunting for the manual.

When the list of codes is short and fixed, you don't need a separate lookup table. Two functions map them right inside the formula:

• CHOOSE(n, a, b, c, …) returns item number n from its list, so CHOOSE(2, "Gold", "Silver") gives Silver
• SWITCH(value, match1, result1, match2, result2, …, default) compares a value with each match in turn, and falls back to the default when nothing matches

This help desk export needs labels that people can read at a glance.

Steps

1. Priority codes run from 1 (most urgent) to 4. In E2:E9, turn each code into a name with CHOOSE: 1 is Urgent, 2 is High, 3 is Normal and 4 is Low.

2. Channel codes are text, so CHOOSE can't help here. In F2:F9, use SWITCH to map EM to Email, PH to Phone and CH to Chat, with Other as the default for anything else.

3. Which day was each ticket opened? WEEKDAY returns 1 for Sunday up to 7 for Saturday, which makes a perfect index for CHOOSE. In G2:G9, nest the two to show short day names such as Mon and Tue.

4. CHOOSE can return numbers too. Urgent tickets need a reply within 1 day, High within 2, Normal within 3 and Low within 5. In H2:H9, add the right number of days to the date each ticket was opened.

Remember

• CHOOSE(n, …) picks item number n, so it suits codes that run 1, 2, 3…
• SWITCH(value, match, result, …, default) maps any exact values, text included.
• Always give SWITCH a default, or unknown codes show #N/A.
• CHOOSE(WEEKDAY(date), "Sun", …, "Sat") turns dates into day names of your choosing.

Previous: Finding duplicates · Next: Text surgery with nested formulas