Skip to main content
SheetTutor.
Use this Directory in the Policy lesson
Lookup principles
  1. Position or field? Try the fixed third-column formula, then change the requested field. MATCH finds that field’s current position.
  2. Move the columns. Reorder the return fields. Move Record into the middle to test VLOOKUP’s leftmost-column rule.
  3. Keep the full record. INDEX/MATCH searches the named Record column. Append a record, query it, then verify all results.

Exact lookups return the first matching record. The examples escape literal *, ? and ~ characters. A missing key or field keeps its formula error; text such as “#N/A” remains text.

Teaching · read-only · Report!A1

Step 1 / 5Follow a complete record

Inspect
More

The first matching Record supplies the result, even when its key uses another case. A later duplicate does not replace the first record. Teaching is read-only; switch to Your turn to edit.

Source!A2 · (blank)
No formula applied

Loading your interactive spreadsheet…

1. Compare lookup formulas

2. Reorder the actual Table

Current order:

Record → Team → Rate → Quota → Status

Every source row moves with its header. One Undo restores the complete Table. VLOOKUP still searches only its first column; INDEX/MATCH follows Directory[Record].

Current complete report

Record keyRequested fieldVLOOKUP / MATCHINDEX / MATCH
—BlankNo formula—BlankNo formula
—BlankNo formula—BlankNo formula
—BlankNo formula—BlankNo formula
Inspect all 7 current source records
objectobjectobjectobjectobject
objectobjectobjectobjectobject
objectobjectobjectobjectobject
objectobjectobjectobjectobject
objectobjectobjectobjectobject
objectobjectobjectobjectobject
objectobjectobjectobjectobject

3. Change a key, field or source value

Edit Report!A2:B4, Report!G2 or Source!A2:E8. Use Text to preserve leading zeroes, wildcard characters and error-looking labels.

4. Append a complete record

Add one row to Directory, then put its Record key in a report query. A repeated key remains a separate row; the first match wins.

Let the Table supply lists and review rules

One Directory Table supplies every record choice, field choice and Rate value. Append a complete record above, move its fields or use worksheet Undo: the names follow the current Table.

Inspect the four workbook names
NameRefers to
RecordChoices=Directory[Record]
FieldChoices=Directory[#Headers]
RateValues=Directory[Rate]
ReviewMinimum=Report!$G$2

These are actual worksheet list rules. The native list checker matches the displayed case exactly. Imported or programmatic writes can bypass entry rejection; their native invalid status remains visible here. The lookup formulas separately match keys without case.

Highlight the complete source row when its Rate is below Report!G2. Edit that numeric minimum with the typed-input controls above. Equality is excluded; zero and refunds stay numeric.

The following is a calculated rule preview, separate from the editable worksheet. It adds no formatting actions to worksheet history. The native workbook copy contains the executable conditional rule.

Attach the two named lists to all six Report inputs.

The rule and list checks are separate from the six lookup answers. Local browser and workbook-package checks do not certify desktop Excel list expansion or edit/save behavior.

Checks use every current query and independent changed-input calculations. Passing this project does not award course completion. Browser and workbook-package checks do not certify native Excel recalculation or edit/save behavior.