Excel Challenge 21 : Map Product Categories

What You Will Learn

Five product codes in A2:A6: "EL-105", "HG-202", "AP-050", "EL-771", "HG-110". The first 2 letters identify the category — EL = Electronics, HG = Home & Garden, AP = Appliances. You need to convert these prefixes into readable category names in column B.

LEFT extracts the prefix, and SWITCH maps it to the full name:

=SWITCH(LEFT(A2, 2), "EL", "Electronics", "HG", "Home & Garden", "AP", "Appliances")

LEFT(A2, 2) returns the first 2 characters — "EL" from "EL-105". SWITCH compares that prefix against each option and returns the matching category name. If the prefix is "HG", SWITCH returns "Home & Garden". If it is "AP", it returns "Appliances". Each row gets its own category based on the first two letters of the code.

The category counts: EL appears twice (EL-105 and EL-771), HG appears twice (HG-202 and HG-110), AP appears once (AP-050). Two products are Electronics.

LEFT extracts the prefix, SWITCH converts it to a readable category.

The audit formulas:

B9: =COUNTA(A2:A6) — total products (5).
B10: =COUNTIF(B2:B6,"Electronics") — how many products are Electronics.

Alternative: Lookup Table with VLOOKUP

SWITCH works for small fixed lists but requires formula edits when new prefixes appear. If the business adds a new product line, you would need to update every SWITCH formula. A lookup table avoids that — store prefix mappings in F2:G4 and use VLOOKUP: =VLOOKUP(LEFT(A2,2),$F$2:$G$4,2,FALSE). Add new prefixes to the table instead of editing formulas.

Alternative: XLOOKUP

XLOOKUP handles missing prefixes with a default value: =XLOOKUP(LEFT(A2,2),$F$2:$F$4,$G$2:$G$4,"Other"). Unknown prefixes return "Other" instead of an error.

Note that the LEFT function extracts the prefix before SWITCH maps it. If the code format changed to use 3-letter prefixes (like "ELE-105"), you would change LEFT(A2,2) to LEFT(A2,3) and update the SWITCH cases. The same pattern works for any fixed-length prefix at the start of a code.

SWITCH requires each prefix and category to be typed directly in the formula. For short fixed lists this is fine. For larger or changing lists, use VLOOKUP or XLOOKUP with a reference table instead.

The COUNTIF in B10 counts how many mapped categories equal "Electronics". With two EL-prefixed codes in the list, the result should be 2.