Challenge Scenario
In retail merchandise hierarchy and product information management (PIM), items in a store catalog are categorized into broad departments (such as "Audio", "Peripherals", or "Displays") based on a master classification lookup table.
By looking up item names against the category master table using VLOOKUP, merchandise managers can automate category assignments across thousands of products without manual tagging.
As an Ecommerce Catalog Specialist, your tasks are:
- Assigned Category (Column B): Look up the Product Name (
A2:A5) in the master lookup table ($F$2:$G$4) using VLOOKUP(A2, $F$2:$G$4, 2, FALSE).
- Department Type (Column C): If Category equals
"Audio", assign "MEDIA"; otherwise assign "HARDWARE".
- Total Catalog Items (Cell B8): Count total products listed using
COUNTA(A2:A5).
- Audio Category Count (Cell C8): Count how many products belong to the
"Audio" category using COUNTIF(B2:B5, "Audio").
Your Sheet Layout
Understanding how the product categorization sheet is structured:
- Product Catalog (Columns A to C): Shows incoming product names, the mapped category from lookup, and the department classification.
- Master Category Mapping Table (Columns F & G): The master dictionary mapping product names (
Headphones, Keyboard, Monitor) to their categories (Audio, Peripherals, Displays).
- Catalog Summary (Rows 7 & 8): Summary KPIs showing total catalog products and count of audio items.
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:A5 |
Product Name |
Catalog Data |
Store product catalog items |
Read-only incoming product list. |
F2:G4 |
Product, Category |
Master Table |
Master category dictionary |
Mapping reference table. Lock with $F$2:$G$4. |
B2:B5 |
Category |
Lookup Mapping |
=VLOOKUP(A2, $F$2:$G$4, 2, FALSE) |
Retrieves the category matching the product name. |
C2:C5 |
Department |
Classification |
=IF(B2 = "Audio", "MEDIA", "HARDWARE") |
Assigns MEDIA to Audio, else HARDWARE. |
B8 |
Total Items |
Summary |
=COUNTA(A2:A5) |
Counts total products in the catalog (4). |
C8 |
Audio Count |
Summary |
=COUNTIF(B2:B5, "Audio") |
Counts products in the Audio category (2). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In Column B (B2:B5), lookup Department Name using VLOOKUP and LEFT prefix matching with $F$2:$G$4.
2
In cell B8, count total SKU lines using COUNTA(A2:A5).
3
In cell C8, sum total stock count for "Beverages" using SUMIF(B2:B5, "Beverages", C2:C5).
4
In cell D8, calculate total inventory count across all items using SUM(C2:C5).
Solution & Formula Breakdown
Let's examine how VLOOKUP and conditional logic map hierarchical catalog data:
1. Retrieving Categories with VLOOKUP (B2:B5)
=VLOOKUP(A2, $F$2:$G$4, 2, FALSE)
The VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) function works as follows:
A2: The product name to look up (e.g. "Headphones").$F$2:$G$4: The locked master reference table.2: Returns the value from the 2nd column of the table (Category).FALSE: Enforces exact matching.- Row 2 (Headphones): Matches → "Audio"
- Row 3 (Keyboard): Matches → "Peripherals"
- Row 4 (Monitor): Matches → "Displays"
- Row 5 (Headphones): Matches → "Audio"
2. Department Assignment with IF (C2:C5)
=IF(B2 = "Audio", "MEDIA", "HARDWARE")
If Category is "Audio", output "MEDIA"; otherwise output "HARDWARE".
3. Catalog Summary Rollups (B8 & C8)
- Total Items (Cell B8):
=COUNTA(A2:A5) returns 4.
- Audio Count (Cell C8):
=COUNTIF(B2:B5, "Audio") returns 2.
Always lock your lookup range ($F$2:$G$4) with dollar signs. If left unlocked, dragging the formula down causes the search table to shift downwards and fail to match earlier entries!