📌 Stage 2 · Estimating · Job Costing · Estimates · 35–45 min

VLOOKUP autofill and the check column that catches typos

Learning goals

  • Enter an estimate as one row per job and cost code
  • Let VLOOKUP pull descriptions and cost types from the Cost Codes sheet
  • Use COUNTIF to flag any line whose code does not exist

Concepts

The estimate is a journal, not a document

Most small contractors build estimates as a printable proposal - which means the numbers die on paper. Here the Estimates sheet is a journal: one row per job and cost code with the estimated cost typed in column F, and the same rows later collect actual labor (column G), actual materials and subs (column H), variance, and burn percentage.

You type exactly three things per line: the Job #, the Cost Code, and the Est Cost. Everything else on the row is derived. That is what makes Lesson 15's estimate-versus-actual drill-down possible with zero extra typing.

💡 Tips:

  • Estimate at the level you can actually control - Ridgeline splits drywall into 3300 labor and 3310 materials because the sub bill and the board bill arrive separately.
  • If you later win the work at a different number, add rows or adjust column F - never overwrite the estimate after costs start landing, or your variance story disappears.

Autofill from the code list

Columns D and E are lookups: they read the code in column C and pull the official description and cost type from the Cost Codes sheet. Nobody types descriptions, so nobody misspells them, and every report that groups by cost type groups correctly.

=VLOOKUP($C5,'Cost Codes'!$B$5:$C$104,2,FALSE)

C5 holds the cost code. VLOOKUP searches the first column of the Cost Codes range for an exact match (FALSE is mandatory - never settle for an approximate match on codes) and returns column 2 of that range, the description. The sheet name is quoted in the reference because 'Cost Codes' contains a space.

=IF(COUNTIF('Cost Codes'!$B$5:$B$104,$C5)=0,"Unknown code","OK")

The Check column counts how many times this row's code appears on the Cost Codes sheet. Zero matches means a typo - VLOOKUP would return #N/A - so the cell says Unknown code instead. Every healthy row reads OK.

When a lookup fails

Type 2900 into a blank row's code cell and the description shows #N/A while the Check column says Unknown code. That pair is the diagnostic pattern for the whole workbook: #N/A means the code is not in the list, and the check column says it in words you can act on.

Fix it once on the Cost Codes sheet if the code should exist, or fix the typo here. Never wrap these lookups in IFERROR to hide the failure - a silently blank description is worse than a visible one.

💡 Tips:

  • In Excel 365 you could write =XLOOKUP($C5,'Cost Codes'!$B$5:$B$104,'Cost Codes'!$C$5:$C$104,"Unknown code") - this course sticks to VLOOKUP so every version since 2010 computes the same answer.

Practice

Fixed case (Mar 1 – Jun 30, 2026). Pale-green cells are formulas. Cell D5 on Estimates has been cleared - you will retype it.

  1. Read the kitchen estimate — The exercise opens on Estimates. Rows 5 through 16 are the kitchen's twelve lines; notice only columns B, C, and F are typed - everything else is derived.
  2. Retype the description lookup in D5 — Cell D5 is blank. Type =VLOOKUP($C5,'Cost Codes'!$B$5:$C$104,2,FALSE) and press Enter. The description General Conditions appears, pulled from the code list.
  3. Verify the cost type lookup — E5 should already read Other for code 1000. Scan down E6 through E16: Subcontract, Labor, Material - the mix that Lesson 6 will roll into a price.
  4. Run the check column — Look at column L: every row reads OK. Then glance at the garage bid lines in rows 34 through 37 - their actual columns are zero because no work has started, but the codes still check out.
  5. See a failure on purpose — In the browser exercise, type 2900 into any empty cell in column C below the data and watch the neighboring check cell flip to Unknown code - then undo or ignore it; browser edits are not saved.

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 D5 and got General Conditions
  • ☐ Confirmed E5 reads Other and the kitchen's twelve estimate lines all show OK in the Check column
  • ☐ Provoked an Unknown code result with a bogus code number