Formula Library Test Me Challenges Sandbox

SWITCH

Logicalintermediate

SWITCH compares one value against a list of possible matches and returns the corresponding result for the first match. Think of it as a cleaner alternative to nested IFs when you're checking one value against many options. Available in Excel 2019+ and Google Sheets.

Syntax

Excel:=SWITCH(expression, value1, result1, [value2, result2], ..., [default])

Google Sheets: =SWITCH(expression, value1, result1, [value2, result2], ..., [default])

Parameters

expressionThe value to evaluate.

value1The first value to match against.

result1The result if expression matches value1.

additional value/result pairs?Extra match/result pairs as needed.

default?The value to return if no matches are found. If omitted and no match exists, SWITCH returns #N/A.

? = optional parameter

Worked Example

Map each invoice status to a priority label: Paid = 'Closed', Pending = 'Follow Up', Overdue = 'Escalate'.

Row numbers	G
Header row	status	priority_label
2	Paid	Closed

Formula

=SWITCH(A2, "Paid", "Closed", "Pending", "Follow Up", "Overdue", "Escalate", "Unknown")

Result

Closed

SWITCH checks the status in A2 against each listed option in order. In this sample, A2 contains 'Paid', so it returns the matching label 'Closed'.

Related:IFS IF AND OR

Practice Exercises

2 exercises - work through each one using the data table below

Exercise 1 of 2beginner

#SWITCH-001

Question

Use SWITCH to map invoice statuses to action labels: 'Paid' = 'No Action', 'Pending' = 'Send Reminder', 'Overdue' = 'Escalate to Manager'. What label would invoice INV-1006 get? (Status: Pending)

Dataset

Showing rows 1-10

Download CSV

Row numbers	A	B
Header row
2	INV-1001	Paid
3	INV-1002	Paid
4	INV-1003	Paid
5	INV-1004	Paid
6	INV-1005	Paid
7	INV-1006	Pending
8	INV-1007	Paid
9	INV-1008	Overdue
10	INV-1009	Paid
11	INV-1010	Paid
12

Click a column name to sort. Use the filter icon to narrow rows by column value.

Scroll to explore the table, then sort or filter by column as needed.

Hints

Your Answer

Exercise 2 of 2intermediate

#SWITCH-002

Question

Use SWITCH to assign a spend category code to each invoice category: 'Software' = 'OPEX-SW', 'Consulting' = 'OPEX-CON', 'Hardware' = 'CAPEX-HW', and everything else = 'OPEX-GEN'. How many unique codes would appear across the dataset? (Count the distinct output values.)

Dataset

Vendor Spend Tracker (150 rows total)

Download CSV

Row numbers	A	B
Header row
2	INV-1001	Software
3	INV-1002	Office Supplies
4	INV-1003	Consulting
5	INV-1004	Travel
6	INV-1005	Marketing
7	INV-1006	Software
8	INV-1007	Hardware
9	INV-1008	Software
10	INV-1009	Marketing
11	INV-1010	Consulting
12	INV-1011	Software
13	INV-1012	Marketing
14	INV-1013	Office Supplies
15	INV-1014	Software
16	INV-1015	Travel
17	INV-1016	Software
18	INV-1017	Office Supplies
19	INV-1018	Consulting
20	INV-1019	Software
21	INV-1020	Hardware
22	INV-1021	Travel
23	INV-1022	Hardware
24	INV-1023	Hardware
25	INV-1024	Office Supplies
26	INV-1025	Marketing
27	INV-1026	Software
28	INV-1027	Software
29	INV-1028	Marketing
30	INV-1029	Marketing
31	INV-1030	Software
32	INV-1031	Marketing
33	INV-1032	Software
34	INV-1033	Software
35	INV-1034	Travel
36	INV-1035	Software
37	INV-1036	Software
38	INV-1037	Software
39	INV-1038	Software
40	INV-1039	Software
41	INV-1040	Office Supplies
42	INV-1041	Consulting
43	INV-1042	Software
44	INV-1043	Marketing
45	INV-1044	Software
46	INV-1045	Travel
47	INV-1046	Software
48	INV-1047	Office Supplies
49	INV-1048	Office Supplies
50	INV-1049	Consulting
51	INV-1050	Software
52	INV-1051	Software
53	INV-1052	Consulting
54	INV-1053	Software
55	INV-1054	Software
56	INV-1055	Marketing
57	INV-1056	Software
58	INV-1057	Software
59	INV-1058	Software
60	INV-1059	Marketing
61	INV-1060	Software
62	INV-1061	Hardware
63	INV-1062	Marketing
64	INV-1063	Travel
65	INV-1064	Travel
66	INV-1065	Software
67	INV-1066	Marketing
68	INV-1067	Software
69	INV-1068	Software
70	INV-1069	Software
71	INV-1070	Office Supplies
72	INV-1071	Marketing
73	INV-1072	Software
74	INV-1073	Consulting
75	INV-1074	Hardware
76	INV-1075	Software
77	INV-1076	Software
78	INV-1077	Travel
79	INV-1078	Consulting
80	INV-1079	Travel
81	INV-1080	Office Supplies
82	INV-1081	Consulting
83	INV-1082	Office Supplies
84	INV-1083	Software
85	INV-1084	Travel
86	INV-1085	Software
87	INV-1086	Software
88	INV-1087	Office Supplies
89	INV-1088	Consulting
90	INV-1089	Software
91	INV-1090	Travel
92	INV-1091	Marketing
93	INV-1092	Software
94	INV-1093	Software
95	INV-1094	Software
96	INV-1095	Marketing
97	INV-1096	Software
98	INV-1097	Marketing
99	INV-1098	Software
100	INV-1099	Travel
101	INV-1100	Travel
102	INV-1101	Marketing
103	INV-1102	Hardware
104	INV-1103	Software
105	INV-1104	Travel
106	INV-1105	Software
107	INV-1106	Consulting
108	INV-1107	Marketing
109	INV-1108	Marketing
110	INV-1109	Software
111	INV-1110	Software
112	INV-1111	Consulting
113	INV-1112	Software
114	INV-1113	Software
115	INV-1114	Travel
116	INV-1115	Software
117	INV-1116	Software
118	INV-1117	Software
119	INV-1118	Office Supplies
120	INV-1119	Software
121	INV-1120	Software
122	INV-1121	Travel
123	INV-1122	Marketing
124	INV-1123	Marketing
125	INV-1124	Software
126	INV-1125	Software
127	INV-1126	Travel
128	INV-1127	Software
129	INV-1128	Marketing
130	INV-1129	Software
131	INV-1130	Software
132	INV-1131	Consulting
133	INV-1132	Software
134	INV-1133	Travel
135	INV-1134	Software
136	INV-1135	Software
137	INV-1136	Office Supplies
138	INV-1137	Travel
139	INV-1138	Office Supplies
140	INV-1139	Consulting
141	INV-1140	Consulting
142	INV-1141	Software
143	INV-1142	Software
144	INV-1143	Office Supplies
145	INV-1144	Software
146	INV-1145	Office Supplies
147	INV-1146	Software
148	INV-1147	Consulting
149	INV-1148	Software
150	INV-1149	Office Supplies
151	INV-1150	Office Supplies
152

Click a column name to sort. Use the filter icon to narrow rows by column value.

Scroll to explore the table, then sort or filter by column as needed.

Hints

Your Answer

Want more practice?

Test Me mode randomizes exercises across all formulas without telling you which formula to use.

Try Test Me ->