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.
That translation becomes important when inventory data is shared outside the system that created the codes. Reports, stock reviews, and category summaries need readable names, but the worksheet still has to respect the short code structure that already exists.
The flow below shows the mapping idea. The product code gives a prefix, the prefix connects to a category rule, and the readable category can then support review and counting.
The Problem: Product Codes Need Human Labels Extracting the prefix lets the sheet translate compact codes into readable categories.
In this workbook, product codes are listed beside blank category cells and a small mapping table. The challenge is to use the code prefix to fill the category name, then complete the summary so the sheet shows total records and the Electronics count.
- The first two characters of each product code identify the category.
- The mapping table stores the readable category labels.
- The summary should count the final category labels.
That makes the worksheet easier to maintain. If the category list grows later, the mapping table is easier to update than hard-coded rules scattered across many rows.
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 direct mapping pattern works well when the code list is short and stable. LEFT extracts the two-letter prefix, and SWITCH turns that prefix into a full category name.
This solves the challenge directly in Excel, but the maintained solution uses the mapping table pattern because it is easier to extend and works reliably in the local engine.
=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.
This is the method used in the solution. 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.
It is useful for compact rules, but a mapping table is often easier for teams to maintain when the code list may grow.
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 same mapping idea can also be built with VLOOKUP, which is the compatible pattern used in the challenge solution.
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.