📌 Stage 1 · Foundations · Sales Pipeline CRM · Contacts and INDEX-MATCH · 35–45 min

People buy, companies pay

Learning goals

  • Keep a contact master sheet with buying roles for each account
  • Write an INDEX-MATCH lookup and explain what each function contributes
  • Choose between VLOOKUP and INDEX-MATCH with reasons, not habit

Concepts

People buy, companies pay

Eleven contacts, CT-01 through CT-11, each tied to a company code. Deals are conversations, and conversations happen with a person: Maya Chen owns Harborview Cafe LLC, Alicia Fontaine runs food and beverage at Cedar Hollow, Sam Ortega signs for the school district. The Buying Role column keeps the sales reality straight - Decision Maker (can sign), Influencer (can kill), User (will live with it). Calling a Decision Maker with a User question, or pitching an Influencer with contract terms, wastes the touch the Activities sheet will later track.

Note that column D stores the company CODE, and the company NAME in column E is a formula. The code stays the durable link back to Companies; the name is just display, computed fresh every time.

=INDEX(Companies!$C$5:$C$104,MATCH($D5,Companies!$B$5:$B$104,0))

MATCH searches Companies column B for the code in D5 and returns its POSITION (for CO-01, position 1). INDEX takes that position and returns the value at the same spot in Companies column C - the company name. The final argument 0 is MATCH-speak for exact match.

💡 Tips:

  • Read it inside-out: MATCH answers 'where?', INDEX answers 'what's there?'. Two small functions, one precise lookup.

Why learn a second lookup pattern

VLOOKUP (Lesson 3) can only return columns to the RIGHT of its search column, and its column index (that 2) silently breaks if someone inserts a column into the master sheet. INDEX-MATCH has neither limitation: the return range is stated independently, so it can look left, right, or across sheets, and it survives column insertions. Performance is also better on large ranges, though at 100 rows you will never notice.

The honest rule: for a two-column master lookup, either works - pick one and use it consistently. This workbook demonstrates both so you can read other people's models, which in real offices contain both.

💡 Tips:

  • In Excel 365 the modern equivalent is =XLOOKUP($D5,Companies!$B$5:$B$104,Companies!$C$5:$C$104) - same result, one function. The course formulas stay classic so every version computes them.

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

  1. Study the contact roster — Rows 5-15: eleven people across ten companies. Note the roles - six Decision Makers, three Influencers, one User - and that E5 is blank pending your formula.
  2. Type the INDEX-MATCH — Click E5 and type =INDEX(Companies!$C$5:$C$104,MATCH($D5,Companies!$B$5:$B$104,0)) then Enter. E5 fills with Harborview Cafe LLC.
  3. Check the last row too — Scroll to row 15: E15 shows Meadowlark School District for Sam Ortega (CO-10) - the same formula, filled down the sheet.
  4. Break it on purpose — Mentally (or in the live sheet, then undo): change D5 to CO-99. MATCH cannot find it and the formula returns #N/A. That is the exact-match contract doing its job.

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 INDEX-MATCH into E5 and got Harborview Cafe LLC
  • ☐ Verified E15 resolves to Meadowlark School District
  • ☐ Can explain MATCH's role (find the position) versus INDEX's role (return the value at that position)