Learning goals

Build order rows that borrow opp number, company, and amount from the quote
Chain VLOOKUPS across three sheets without losing track of the source
Verify the five orders total $70,200 - identical to the won-value dashboard figure

Concepts

Three lookups, one typed key

An order row types exactly two facts: the order number and the quote number. Everything else is borrowed. The Opp # comes from the quote's column D. The company comes from that opportunity's row. The amount comes from the quote's amount column. Each lookup is small; the chain is powerful - a price corrected on the quote flows to the order and (next lesson) to the invoice without a single re-typed digit.

=VLOOKUP($D5,Quotes!$B$5:$G$1004,6,FALSE)

SO-4001's amount: find the quote number from D5 (QT-3001) in Quotes column B and return column 6 of the B:G range - the Amount. Result: $6,500.00.

=VLOOKUP($E5,Opportunities!$B$5:$D$1004,3,FALSE)

SO-4001's company: take the Opp # just derived in E5 (OPP-101) and borrow the company name from the opportunity register. Result: Harborview Cafe LLC.

💡 Tips:

Counting columns by hand is how VLOOKUP breaks. B:G means B is 1, C is 2 ... G is 6 - write it on paper once and the index stops being mysterious.

One source of truth for money

The $6,500 Harborview equipment order exists as a typed number exactly once - on the quote. Order copies it; the invoice (Lesson 17) will copy the order. Typing $6,500 twice is how $6,050 ships and how an invoice disputes start. The five orders total 6,500 + 12,000 + 28,000 + 15,500 + 8,200 = $70,200, which equals the Dashboard's Won value exactly - every won deal became an order. When those two numbers disagree in your own workbook, one of two things is true: a won deal was never ordered, or an order exists without a won deal. Both are worth finding.

Status vocabulary for orders: Open (entered), In Fulfillment (being prepared), Invoiced (billed - all five sit here), Closed (delivered and paid). The Invoice # column closes the loop forward.

💡 Tips:

If a price changes after acceptance, change the QUOTE with a revision row and let the chain flow - never patch the order in place.

Practice

Fixed case (Jan-Jun 2026, aging date Jun 30, 2026). Pale-green cells are formulas; white cells are typed entries. This lesson opens on Orders.

Type the amount lookup — G5 is blank. Click it and type =VLOOKUP($D5,Quotes!$B$5:$G$1004,6,FALSE) then Enter. SO-4001 returns $6,500.00 - borrowed from quote QT-3001, never re-typed.
Read the derived columns — Same row: E5 shows OPP-101 (from the quote) and F5 shows Harborview Cafe LLC (from the opportunity). Two hops, both visible.
Total the orders — In a scratch cell below the table (row 11 works) type =SUM($G$5:$G$9) - it returns 70,200.
Reconcile with the Dashboard — Flip to Dashboard: Won value = $70,200.00, identical. Every won deal became an order and every order carries an invoice number in column I - the chain is intact.

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:

☐ G5 returned $6,500.00 via the six-column VLOOKUP into Quotes
☐ Traced E5 = OPP-101 and F5 = Harborview Cafe LLC through the chain
☐ Summed the five orders to $70,200 and matched it to the Dashboard's Won value

📈 Lesson 16 · Orders from accepted quotes