Syntha Lab RU

Excel for retail, part I: lookup by key, pivot tables and data validation

The first part of the “Excel for retail” track teaches the three techniques on which most reports of a buyer, a planner and an analyst rest: linking two tables by a key (VLOOKUP, INDEX and MATCH, XLOOKUP), summarizing thousands of rows with a pivot table and checking that the data are not broken. The document shows the formulas on an illustrative export, the order of actions and control checks so that the pivot total matches the source table; the formulas are written for the Russian Excel interface with the “;” separator.

Production, logistics, channels Brand and retail Verified: 2026-11-15

Open interactively →

Why this document

Most of an analyst’s time goes not on analysis but on collecting data from different exports and making sure they agree. One broken style code or a number stored as text turns a report into a beautiful error, and a purchase decision is taken by it. The document gives the minimum of techniques after which a table becomes reliable: key, lookup, pivot, control. It is read by a buyer, a planner, an analyst and the brand’s commercial director who combines sales by channel.

At a glance

What is this document and why is it needed?
The first part of the “Excel for retail” track teaches the three techniques on which most reports of a buyer, a planner and an analyst rest: linking two tables by a key (VLOOKUP, INDEX and MATCH, XLOOKUP), summarizing thousands of rows with a pivot table and checking that the data are not broken. The document shows the formulas on an illustrative export, the order of actions and control checks so that the pivot total matches the source table; the formulas are written for the Russian Excel interface with the “;” separator.
Who owns the document and how often is it rebuilt?
Kept by an analyst or a planner; the file works as a workbook: source exports on separate sheets, calculations on their own, a control sheet with checks; passed to the buyer and the manager together with the control.
What mistakes are the most common?
Brand: Search style codes without cleaning: a trailing space or a number-text gives #N/A and the row drops out of the report without warning. Brand: Use VLOOKUP without a zero in the last argument: the lookup returns the nearest value rather than the exact one and someone else’s price is pulled in. Brand: Hide errors with IFERROR and not count them: data are lost while the report looks clean.

For whom

Brand
A brand has to combine sales by channel (wholesale, site, platform), the warehouse and production: the key is the model style code with size and color, and there are more sources than in a store.
Retail
A retailer has to combine sales by stores and categories with stocks by warehouses and a reference of matches between brand style codes and the store’s barcodes; there are more data and the keys differ among suppliers.

What it consists of

Preparing the data: a flat table and a key

  • A flat table: one header in the first row, one record per row, no merged cells, empty rows or totals inside; such a table is accepted by a pivot and lookup functions.
  • A smart table (Insert → Table): the range grows by itself, formulas fill down and formulas can use column names instead of addresses.
  • Brand: The key is the model style code, with size and color through a hyphen if needed; one rule of writing for all sheets, otherwise the lookup will not find the row.
  • Retailer: The key is a barcode or the store’s SKU; the brand’s style code lies in a reference of matches, not typed by hand into sales.
  • Cleaning: TRIM removes extra spaces, CLEAN non-printable characters, VALUE turns text into a number; the cleaned values go into a neighboring column, the source is not touched.

Lookup by key

  • VLOOKUP(key; table; column number; 0): finds the key in the first column of the table and returns the value from the specified column; the last argument 0 means an exact match, without it the lookup gives a random answer.
  • INDEX(result column; MATCH(key; key column; 0)): looks to the left and does not break when columns are inserted; more reliable than VLOOKUP in workbooks that are changed.
  • XLOOKUP(key; key column; result column; “none”): a modern replacement for both, available in Excel 365 and newer versions; older versions do not have it, check before passing the file on.
  • IFERROR(formula; “no data”): hides the #N/A error but must not hide the problem: the number of such rows is counted separately in the control.
  • SUMIFS(sum; range1; condition1; range2; condition2): sums sales by several conditions, for example category and month; COUNTIF counts rows by a condition.
  • Brand: Lookup pulls the model’s price and cost from the reference to the channel’s sales so as to count the margin by rows.
  • Retailer: Lookup pulls the warehouse stock and the status of the position to the store’s sales to see where a reorder is needed.

Pivot tables

  • Insert → Pivot table: rows are the category or the brand, columns the month or the week, values the sum of sales and the sum of stock; there are no formulas, the table counts the totals itself.
  • Grouping dates by months, slicers for filtering by brand or store, values as a share of the total: three techniques that are enough for most reports.
  • After editing the source data the pivot is refreshed (Data → Refresh all); the table does not recalculate itself, and an outdated pivot is a frequent cause of discrepancies.
  • Brand: A pivot by channels and months shows where revenue falls and returns grow; data for the media plan are built from it.
  • Retailer: A pivot by stores and categories shows where sales and stock diverge; the list of positions for transfer and reorder comes from it.

Data validation and control

  • Data → Data validation: a list of allowed values for the category and the channel, a number not below zero for the stock, a date in the permitted range; the error message explains what to enter.
  • Input validation does not catch what is pasted ready-made: exports are checked with formulas, for example COUNTIF(A:A; A2) > 1 for duplicates and with conditional formatting.
  • The control sheet: the number of rows in the source and in the pivot, the sum of sales in both, the number of keys without a match, the number of errors; the difference must be zero in all checks.
  • Brand: The control includes a reconciliation with platform data: the channel total in the table equals the platform report, a discrepancy is explained before use.
  • Retailer: The control includes a reconciliation with the cash register and the accounting system: the store’s total equals the cash total for the day.

Where the data comes from

Data sourceFields
Exports from accounting systems and platformsSales by style codes, stocks by warehouses, prices, returns; dates and keys in one format.
The style referenceThe key, category, brand, price, cost, status, barcodes and matches.
Control rulesAllowed values, thresholds, the number of rows and control sums of the source.

How to build it step by step

  1. Step 1: Brand: list all the sources (accounting, warehouse, site, platforms) and define a common key: the model style code, with size if needed.
  2. Step 2: Retailer: define the store’s key (barcode or SKU) and create a reference of matches with brands’ style codes.
  3. Step 3: Bring the exports to flat tables: remove merges and totals, format them as smart tables, add a column of the cleaned key.
  4. Step 4: Count the number of rows and the totals of the source tables and write them on the control sheet: this is the reference for all further checks.
  5. Step 5: Pull the reference data by lookup (INDEX and MATCH or XLOOKUP); count the number of keys without a match and examine each.
  6. Step 6: Build a pivot table on the needed fields and check that its total equals the source table’s total to zero.
  7. Step 7: Add data validation on the input sheets (lists, numbers not below zero, dates) and conditional formatting of duplicates.
  8. Step 8: Count an indicator by rows, for example coverage in weeks, and add a status by thresholds; put the thresholds in separate cells, not in the formula.
  9. Step 9: Protect the sheets with formulas, leave the input in separate cells and save the control sheet with the date of the export; start the next recalculation with the check.

Formulas

MeasureFormulasNote
Coverage in weeks=IFERROR(stock / weekly sales; “no sales”)You cannot divide by zero: zero sales give a separate status.
Status by thresholds=IF(coverage < low; “reorder”; IF(coverage > high; “excess”; “normal”))The thresholds (here an illustrative 4 and 16 weeks) lie in cells: they are set by the category and the delivery term.
Sum by two conditions=SUMIFS(sales; category; “Dresses”; month; “March”)The criteria are given as cell references so as not to edit the formula.
Control difference=SUM(source sales) − the sum in the pivot tableIt must be zero; any other value is a reason to stop.

Formulas in notation

Coverage
K = Q / S
Q is the stock in units; S is the weekly sales; K is the coverage in weeks; at S = 0 the coverage is undefined.

Worked example

Reconciliation of style codes: sales, stock, coverage and status, an illustrative export

Style codeWeekly sales, unitsStock, unitsCoverage, weeksStatus
D-101423007.1normal
D-1021542028excess
T-210601803reorder
T-2118202.5reorder
S-305251255normal
S-306090—no sales

Coverage equals stock divided by the week’s sales; the thresholds are illustrative: under 4 weeks — reorder, over 16 — excess. Of six style codes one has no sales (coverage is undefined, 90 units of stock lie idle), 2 style codes need a reorder, 1 have an excess. All numbers are illustrative.

Figures

The path of data from export to report. The control stands at every step, not at the end: an error found after the pivot costs more.

  1. Export → Cleaning and key
  2. Cleaning and key → Lookup in the reference; Control sheet
  3. Lookup in the reference → Pivot table; Control sheet
  4. Pivot table → Control sheet
  5. Control sheet → Report and decision
  6. Report and decision
Errors in an illustrative export of 1,000 rows, number of rows
Bar chart: Spaces in style codes 38, A number stored as text 21, Duplicate style codes 12, Empty keys 9, Different spellings 17010203040Spaces in style codesSpaces in stylecodesRows: 3838A number stored as textA number stored astextRows: 2121Duplicate style codesDuplicate stylecodesRows: 1212Empty keysEmpty keysRows: 99Different spellingsDifferent spellingsRows: 1717rows
Data as a table
MeasureRows
Spaces in style codes38
A number stored as text21
Duplicate style codes12
Empty keys9
Different spellings17

Errors in an illustrative export of 1,000 rows, number of rows. 97 of 1,000 rows have errors: spaces and text numbers interfere most often; all of them are caught by cleaning and a check before the lookup. The row counts are illustrative: an example of the structure of errors, not a measured share.

Specifics by category

Apparel
In apparel the key is often compound (model, size, color), so the first step is a single key column with a check for duplicates. Pivots are built by models and sizes, and coverage is counted by model, not by size, otherwise rare sizes give a false excess.
Footwear
In footwear the key includes the size and the size run gives many rows: a pivot by sizes shows holes in the run. Coverage is counted by the model-size pair and by the model as a whole; data control matters because of sizes stored as text (“38.5” against a number).
Accessories
In accessories there are few sizes but many colors and materials, and the key is often built by color; there are fewer rows per model and the turnover is higher. The coverage thresholds for accessories differ and are set in cells, not in the formula.
Kids
In children’s sizes go by height and the key includes the age group; the season shifts because the child grows. Data control checks that size by height and size by age are not mixed up, and coverage is counted by age groups.
Men
In the men’s segment there are more basic positions with a long life: weekly sales are steadier and coverage in weeks reads more reliably. Lookup goes by style code and the thresholds for basic positions are higher than for fashion ones.
Women
In the women’s segment there are more fashion positions and short seasons: weekly sales jump and a moving average rather than one week is counted in the table. The coverage thresholds for fashion positions are lower, and a pivot by category and season slicers are needed every week.

Common mistakes

  • Brand: Search style codes without cleaning: a trailing space or a number-text gives #N/A and the row drops out of the report without warning.
  • Brand: Use VLOOKUP without a zero in the last argument: the lookup returns the nearest value rather than the exact one and someone else’s price is pulled in.
  • Brand: Hide errors with IFERROR and not count them: data are lost while the report looks clean.
  • Retailer: Not refreshing the pivot after a new export: the figures in the report are old while the decision is made on ones that look fresh.
  • Retailer: Merge cells and insert totals inside the table: a pivot and lookup do not read such a table.
  • Retailer: Not reconciling the total with the cash register or the accounting system: the discrepancy accumulates until it is found at the close of the period.

Russian specifics

  • Brand: Style codes and barcodes in your accounting system and in marking must match: a key mismatch between the table and the marking system breaks at the shipment stage; check the marking rules by product group.
  • Retailer: Cash register data fall under Law No. 54-FZ on the use of cash registers; exports for Excel are taken from the accounting system and cash data are not edited by hand. Rule: Law 54-FZ of 22.05.2003: Federal Law of 22.05.2003 No. 54-FZ on the use of cash registers.
  • Brand: Tables with clients’ personal data (names, phones) in Excel are stored with restricted access; the requirements of Law No. 152-FZ on personal data apply to working files too. Rule: Law 152-FZ of 27.07.2006: Federal Law of 27.07.2006 No. 152-FZ on personal data.
  • Retailer: The formulas are written for the Russian Excel interface (separator “;”, comma as the decimal mark); in the English interface the separator is “,” and function names are English, check the names and availability of XLOOKUP in your version.

Open interactively →