SWITCH for department budget codes
IntermediateWhen a quarterly budget report uses short department codes, SWITCH is a clean way to translate each code into a readable label. It is often easier to audit than a long chain of nested IF statements because each exact match is paired directly with the result you want to return.
The SWITCH function works like this:
=SWITCH(value, match1, result1, match2, result2, ..., default)
It checks one value, looks for an exact match in your list, and returns the corresponding result. This is useful when one code should always map to one label.
Your task
In this budget review, column E already shows each employee's quarterly budget amount. Your job is to label each budget line correctly by translating the department codes in column B for each employee row. Use the lookup table in A12:C16 as your mapping guide.
- In C2, use SWITCH to return the full department name for the code in B2, using the mappings shown in A12:B16.
- In D2, use SWITCH again to return the budget category for the same code, using the mappings shown in A12:C16.
- Fill both formulas down through row 9.
Use one SWITCH formula for the department name column and a separate SWITCH formula for the budget category column. The same department code should drive both outputs, but each column needs its own result list.
What you should see
When you're done, each employee row should show the correct department name in column C and the correct budget category in column D, based on the code in column B.
Need some help?
Hint 1
SWITCH starts by checking one cell against a list of exact matches. In the first employee row, that cell is B2.
Hint 2
Solve column C first in C2, then copy it down. The repeated department codes in column B will help you quickly see whether your mapping is working.
Hint 3
Column D uses the same department code as column C, but returns a different label set. You are not changing what you match on, only what each match returns.
SWITCH for department budget codes
IntermediateWhen a quarterly budget report uses short department codes, SWITCH is a clean way to translate each code into a readable label. It is often easier to audit than a long chain of nested IF statements because each exact match is paired directly with the result you want to return.
The SWITCH function works like this:
=SWITCH(value, match1, result1, match2, result2, ..., default)
It checks one value, looks for an exact match in your list, and returns the corresponding result. This is useful when one code should always map to one label.
Your task
In this budget review, column E already shows each employee's quarterly budget amount. Your job is to label each budget line correctly by translating the department codes in column B for each employee row. Use the lookup table in A12:C16 as your mapping guide.
- In C2, use SWITCH to return the full department name for the code in B2, using the mappings shown in A12:B16.
- In D2, use SWITCH again to return the budget category for the same code, using the mappings shown in A12:C16.
- Fill both formulas down through row 9.
Use one SWITCH formula for the department name column and a separate SWITCH formula for the budget category column. The same department code should drive both outputs, but each column needs its own result list.
What you should see
When you're done, each employee row should show the correct department name in column C and the correct budget category in column D, based on the code in column B.
Need some help?
Hint 1
SWITCH starts by checking one cell against a list of exact matches. In the first employee row, that cell is B2.
Hint 2
Solve column C first in C2, then copy it down. The repeated department codes in column B will help you quickly see whether your mapping is working.
Hint 3
Column D uses the same department code as column C, but returns a different label set. You are not changing what you match on, only what each match returns.