Learning goals

Structure an account master sheet: one row per company, one stable code per row
Pull a company name from its code with VLOOKUP exact match
Explain why legal-form suffixes (LLC, PC, Inc, LLP) belong on the master sheet

Concepts

Stable codes beat typed names

Ten accounts, CO-01 through CO-10, one row each. The code is the only thing downstream sheets ever reference; the display name can change freely. If Harborview Cafe LLC rebrands tomorrow, you edit one cell here and every opportunity, quote, order, and invoice shows the new name - that is the entire point of master data. Typed names, by contrast, drift: 'Harborview', 'Harborview Cafe', and 'Harborview Cafe LLC' become three different companies the moment someone skips the dropdown.

Realistic accounts for a Pacific Northwest roastery: cafes (Harborview Cafe LLC), a dental practice (Bright Path Dental PC), a hotel group (Cedar Hollow Hotel Group), a school district (Meadowlark School District). The suffix is not decoration - LLC, PC, Inc, and LLP signal legal form, which affects invoicing (who signs, W-9 details for US vendors) and how formally terms are enforced.

=VLOOKUP($C5,Companies!$B$5:$C$104,2,FALSE)

Take the company code in C5, find it in the first column of Companies B:C, and return column 2 of that range - the company name. FALSE forces an exact match: an unknown code surfaces as #N/A instead of silently matching a neighbor.

💡 Tips:

#N/A here is a feature, not a bug - it is the loudest possible signal that a code was typed instead of picked from the dropdown.

In Excel 365 you could write =XLOOKUP($C5,Companies!$B$5:$B$104,Companies!$C$5:$C$104) - this course sticks to VLOOKUP so every Excel since 2010 works.

What belongs on a master sheet - and what does not

Columns: Code, Company Name, Industry, City, State, Account Owner, Notes. Owner matters when a business has more than one seller - Dana Whitfield, Marcus Lee, and Priya Nair each carry accounts here, and COUNTIFS by owner is one formula away. Notes capture selling context (committee sign-off above $10,000, budget-conscious crews) that a new hire would otherwise learn the hard way.

What never goes here: deals, dates, or dollars. Master data is slow-changing; anything that changes per transaction belongs on a register sheet that references the code. Mixing them is how workbooks rot.

💡 Tips:

The $10,000 committee threshold in CO-05's notes explains why the Cedar Hollow deal took 98 days - context rows like that are worth more than a dashboard widget.

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 Companies.

Study the ten accounts — Rows 5-14: note the mix of LLC, PC, Inc, and LLP suffixes, the two states (OR and WA), and the three account owners. C12 (Foxglove Events Inc) is blank - you will restore it.
Re-type the missing name — Click C12 and type Foxglove Events Inc exactly, then Enter. One row of master data, repaired.
Watch the code do its work — Switch to Opportunities: column D shows company names, but only column C (codes) was ever typed. Find OPP-105 (row 9) and OPP-110 (row 14) - both show Foxglove Events Inc, pulled from the row you just fixed.
Count the reach — Back on Companies, note that all ten accounts are US small-business or public-sector buyers across Oregon and Washington - the realistic selling radius of a Portland roastery.

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 Foxglove Events Inc into Companies!C12
☐ Verified Opportunities column D (rows OPP-105 and OPP-110) resolves names from Companies codes
☐ Can explain why codes stay stable for the life of the account while names may change

📈 Lesson 03 · Companies: master data done right