📌 Stage 2 · Recipes and Purchases · Inventory & COGS · Bill of materials · 30-40 min

One row per material per SKU, with names filled in by VLOOKUP

Learning goals

  • Structure a bill of materials as one row per material per SKU
  • Pull the material name from the Materials sheet with an exact-match VLOOKUP
  • Read quantity per unit across jars, sets, and fractional weights

Concepts

A recipe is a table, not a paragraph

The BOM sheet holds 33 lines: every material in every SKU. A Campfire Pine jar takes half a pound of wax, 0.6 fluid ounces of fir needle oil, one amber jar, one wick, one label, and one mailer. The two-jar gift set doubles the candle contents and adds a gift box. The trio set takes one of each scent plus a box.

Long-form recipe notes are unreadable to formulas. One row per material per SKU means SUMIF can roll a whole recipe up in one cell, and the same table answers what a batch consumes.

VLOOKUP fills in the names

You type a material code; the name appears. VLOOKUP searches the first column of a range for an exact match and returns a value from a column you number. Here the range starts at Materials column B (codes) and the name sits in the second column, so the column index is 2.

FALSE means exact match, always. An approximate match on unsorted codes returns confident nonsense.

=VLOOKUP($D5,Materials!$B$5:$C$104,2,FALSE)

Take the material code in D5, find it in the first column of Materials!B5:C104, and return the name from column 2 of that range. FALSE forces an exact match, so an unknown code surfaces as #N/A instead of a silent wrong answer.

💡 Tips:

  • Count columns from the start of the lookup range, not from column A of the sheet.
  • The ranges run to row 104 for master data and row 1004 for journals, so new rows are already inside every formula.

Practice

Fixed case (Sep 2026). The exercise opens on BOM. Pale-green cells are formulas.

  1. Read a recipe — Rows 5 through 10 are JG-01: six lines from 0.5 lb of soy wax down to 1 mailer. Rows 29 through 36 are the JG-05 trio with 1.5 lb of wax and three jars.
  2. Type the name lookup — Cell E5 is blank. Type =VLOOKUP($D5,Materials!$B$5:$C$104,2,FALSE) and press Enter. Soy wax appears.
  3. Fill down — Fill E5 down through E37. All 33 material names resolve, from Soy wax to Recycled paper mailer.
  4. Spot a bad code — Temporarily change D5 to RM-99 and note the #N/A. That error is your friend: it says the code does not exist. Undo back to RM-01.

Online exercise

The spreadsheet below is editable — follow the practice steps right on this page. Steps that need ribbon menus should be done in your own Excel with the downloadable practice file.

表格加载中…

Checklist

Work through each item; when every box passes, this lesson is done:

  • ☐ Typed the VLOOKUP into BOM!E5 and got Soy wax
  • ☐ Filled the name column down through all 33 recipe lines with no #N/A
  • ☐ Can explain why one row per material per SKU beats recipe notes in a single cell
  • ☐ Verified the baseline: 33 BOM lines, JG-01 wax 0.5 lb, JG-05 wax 1.5 lb