The Problem
Short product codes are useful for systems, but they are not always useful for people reading a report. A prefix like EL may make sense to the warehouse team, yet the wider business usually wants to see a full category name.
This challenge is about translating those short prefixes into readable labels. Once the category names are filled in, the sheet becomes easier to scan and the summary at the bottom becomes much clearer.
How We Solve It
The first step is to extract the first two characters from the product code. That prefix is the part that identifies the category, so we can map it to a full label with either a direct formula rule or a lookup table.
Method 1: SWITCH for a direct mapping
Method 1: Map the prefix directly inside the formula with SWITCH.
This is the method used in the solution. LEFT extracts the two-letter prefix, and SWITCH turns that prefix into a full category name. It works well when the code list is short and stable.
This solves the challenge directly because each product code already carries the category hint in its first two characters. Once that prefix is pulled out, SWITCH can translate it into the readable label the worksheet needs in column B.
=SWITCH(LEFT(A2,2),"EL","Electronics","HG","Home & Garden","AP","Appliances")
Method 2: Use a lookup table
Method 2: Use a lookup table when the code list may grow later.
If the company adds more prefixes over time, a mapping table is easier to maintain than a long formula. The prefix is still extracted first, but the lookup table keeps the mapping rules out of the main cell formula.
This solves the same mapping problem in a more scalable way. Instead of editing the formula every time a new code appears, the team can update the mapping table and let the lookup return the right category from there.
=VLOOKUP(LEFT(A2, 2), $F$2:$G$4, 2, FALSE)
Method 3: XLOOKUP for a modern version
Method 3: Use XLOOKUP for a more flexible modern mapping pattern.
XLOOKUP does the same kind of mapping with a cleaner structure and a built-in fallback value. It is a good choice when you want more flexibility or expect the lookup setup to change later.
This solves the category problem with a more modern lookup pattern. It still starts from the extracted prefix, but it adds a fallback result and keeps the mapping logic easier to expand if the product code list changes later.
=XLOOKUP(LEFT(A2, 2), $F$2:$F$4, $G$2:$G$4, "Other")
Function Explanation
1. LEFT
LEFT returns a chosen number of characters from the start of a text string. In this challenge, it isolates the category prefix from each product code.
Learn more this functionLEFT
2. SWITCH
SWITCH compares one value against a series of options and returns the matching result. It keeps a short mapping formula readable when the list of codes is small.
Learn more this functionSWITCH
3. XLOOKUP
XLOOKUP searches for one value and returns a result from another range. It is useful when the mapping needs to live in its own table instead of inside the formula.
The important part is making sure the extracted prefix matches the structure of the product codes. If the code format changes, the first step to revisit is usually the LEFT part.
Translate each product-code prefix into its full category name, then finish the summary so the sheet shows the total number of records and how many items belong to the Electronics category.