Data tables, lookups, and aggregation
Connect sales to product details, resolve missing or duplicate records, and reconcile category totals.
Join a small sales table to a price or category table and summarize results while checking for missing and duplicate records.
Overview
A manager at established retailer Summit Outdoors wants revenue by category. Her sales export contains codes and quantities; a separate product list supplies categories and prices.
Both businesses and all figures in this lesson are fictional teaching assumptions.
Recall Lesson 030: Why did $B$3 stay fixed when the variable-cost formula was copied across months?
Give each row a clear meaning
A data table organizes records into rows and named fields into columns. Here, each sales row represents one completed sale of one product. Each product-list row represents one product. Here, “data table” means ordinary tabular records.
A key identifies a record. Sale ID identifies a sale; product code identifies a product in the product list. A product code may appear in many sales because customers can buy that product repeatedly. It should appear only once in this simple product list.
A lookup searches for a key and returns related information. A join connects information from tables using their shared key. Here, use the product code to attach category and price to each sale. Matching by row position would fail as soon as the lists had different orders.
Keep codes consistent, quantities numeric, and subtotals outside the source records. A code with an extra space may fail to match. Preserve the original export so corrections can be traced.
Worked example
These are all Summit's sales for one reporting week. Assume the listed prices applied to every sale, with no discounts, returns, or taxes included. Costs have not been supplied.
Place the product list in J1:L4, with headers in row 1:
| Product code, J | Category, K | Price per unit, L |
|---|---|---|
| P1 | Camp | 20 |
| P2 | Camp | 10 |
| P3 | Travel | 15 |
Place sales in A1:C5:
| Sale ID, A | Product code, B | Units, C |
|---|---|---|
| 101 | P1 | 2 |
| 102 | P2 | 3 |
| 103 | P1 | 1 |
| 104 | P3 | 4 |
Add headers Category, Price, and Revenue in D1:F1. In E2, enter =XLOOKUP(B2,$J$2:$J$4,$L$2:$L$4,"MISSING",0) and copy down through E5.
The formula searches for B2's product code in J2:J4 and returns the price from the corresponding row in L2:L4. The dollar signs keep those ranges fixed as you copy down. "MISSING" is the result if no match exists; 0 requests an exact match. XLOOKUP normally returns the first match, so a returned price does not prove the product code is unique. XLOOKUP guide.
For category, enter =XLOOKUP(B2,$J$2:$J$4,$K$2:$K$4,"MISSING",0) in D2 and copy down. In F2, enter =C2*E2 and copy down. The four revenues are 2 × $20 = $40; 3 × $10 = $30; 1 × $20 = $20; and 4 × $15 = $60.
These formulas use English names and comma separators. You can work the matches by hand if your spreadsheet lacks XLOOKUP; Microsoft notes that Excel 2016 and 2019 do not support it.
Check matches before summarizing
COUNTIF counts cells meeting a condition. In G2, enter =COUNTIF($J$2:$J$4,B2) and copy down. This counts how many product-list entries match each sale's code. One means a unique match, zero means missing, and more than one means duplicated product records. COUNTIF guide.
Separately, =COUNTIF($A$2:$A$5,A2) checks how often that sale ID appears. Investigate counts above one. Do not remove rows merely because their product codes repeat: sales 101 and 103 are different sales of P1.
Resolve missing or conflicting entries against source records before reporting totals. Never replace an unknown price with zero. Text such as MISSING makes the problem visible, but summary formulas can ignore text or omit unclassified rows. Check that every sale is resolved; do not rely on a plausible total.
Aggregate the results
Aggregation combines detailed records into summaries. SUMIF adds amounts whose corresponding rows meet a condition. For Camp revenue, use =SUMIF(D2:D5,"Camp",F2:F5): inspect categories in D2:D5, select Camp, and add the matching revenues in F2:F5. SUMIF guide.
Camp totals $40 + $30 + $20 = $90. Travel totals $60. The categories reconcile to $150, matching =SUM(F2:F5). There are still four sales rows after attaching product details, and their quantities total ten units.
Reconciliation means showing that independently organized totals agree. It catches some omissions, but an incorrectly duplicated sale could appear in both totals. Check keys as well as arithmetic. Revenue also does not establish profit without costs.
Exercise
Allow 8–10 minutes. You own Fern Craft Supplies. Review these draft records for one day, using the same no-discount, no-return, and no-tax assumptions. Prices are dollars per unit.
| Draft product code | Category | Price |
|---|---|---|
| S1 | Repair | 30 |
| S2 | Care | 10 |
| S2 | Care | 12 |
| Draft sale ID | Product code | Units |
|---|---|---|
| 201 | S1 | 2 |
| 202 | S2 | 3 |
| 202 | S2 | 3 |
| 203 | S3 | 4 |
Source invoices confirm that sale 202 occurred once and was imported twice. The approved price list confirms S2 was $10 that day; its $12 entry is erroneous. It also confirms S3 belongs to Care and cost customers $5 per unit. The other entries are correct.
- Identify the duplicated sale, duplicated product key, and missing product. Explain why a first-match lookup could hide a problem.
- State the corrections, then attach category and price to each valid sale and calculate its revenue.
- Summarize revenue by category and reconcile to the grand total. Report the valid sale count and total units.
- Name two checks to perform before relying on the summary. Could today's product prices safely value last year's sales without further evidence?
Try the exercise before reading on.
Answer
Sale ID 202 appears twice. Product code S2 has two conflicting prices; S3 is absent. A first-match lookup could return $10 for S2 and conceal the conflicting $12 record. A different row order could change the returned price.
Keep one sale 202, remove the erroneous S2 price entry from the working product list, and add S3 with category Care and price $5. Preserve the original data and document the invoice and price-list evidence for each correction.
| Valid sale | Category | Calculation | Revenue |
|---|---|---|---|
| 201 | Repair | 2 × $30 | $60 |
| 202 | Care | 3 × $10 | $30 |
| 203 | Care | 4 × $5 | $20 |
Repair revenue is $60. Care revenue is $30 + $20 = $50. Total revenue is $110, matching $60 + $30 + $20. The corrected data contain three sales and 2 + 3 + 4 = nine units.
Check that each sale ID occurs once and every sales product code matches exactly one product-list row. Then compare category totals with detailed revenue. Keeping the duplicated sale would overstate revenue by $30 even if the category totals reconciled.
Today's price list is insufficient for older sales unless those prices actually applied. Use transaction prices or a price history matched to the sale date. A product-only lookup assumes one applicable price per product for the chosen period.
Recall answer: Dollar signs made the reference absolute, preserving the common input when copied. Today's lookup ranges stay fixed for the same reason, while B2 changes to B3 for the next sale.
Applying this lesson
Add a small product list and sales detail to your model. Check keys and missing matches before calculating category totals. Tomorrow you will compare alternative assumptions with scenarios.
Sources
Comments
Loading comments…