Created by the First Role Skills Editorial Desk. This lesson uses a fictional workplace scenario and links to primary product documentation where the interface or feature matters.
Start with the work
A realistic scenario
A request list contains ticket IDs, but the owner names live in a separate reference table. You need to find missing assignments without copying names by hand.
Avoid rework
Three beginner mistakes
Matching on names instead of stable IDs
Replacing every #N/A with a blank
Using approximate match on unsorted business data
The method
Complete it in six controlled steps
- 01
Protect the raw data
Duplicate the workbook or add a read-only Raw sheet. Never begin an audit by overwriting the only copy of the source data.
- 02
Choose the lookup key
Use a unique, stable field such as Ticket_ID. Check for blanks and duplicates before writing any formula. Names and descriptions are poor keys.
- 03
Align the data types
A numeric 1042 and the text '1042' may look identical but fail to match. Standardize both key columns before diagnosing the formula.
- 04
Write an explicit formula
Use XLOOKUP with an if-not-found message such as REVIEW. If compatibility requires VLOOKUP, use FALSE for an exact match and lock the table range.
- 05
Investigate unmatched rows
Treat an unmatched result as information. Check spaces, typing differences, archived records, and new IDs. Do not invent an owner to make the error disappear.
- 06
Summarize the audit
Count matched, unmatched, blank, and duplicate keys. Add a note describing the source tables, formula, review date, and unresolved exceptions.
See the difference
Before and after
Ticket 1042 -> copied owner name Ticket 1043 -> blank No record of the source or review decision
=XLOOKUP(A2,Owners!A:A,Owners!B:B,"REVIEW") Matched: 8 | Review: 2 | Duplicate keys: 0
Original practice
Complete the exercise
Download the two-table sample. Return each ticket's owner, flag unmatched IDs as REVIEW, and write a three-line audit summary. The answer is included as a separate CSV.
Use only fictional or fully anonymized data. Never upload an employer's confidential information to a portfolio.
Explain your decisions
Turn the exercise into credible evidence
A strong audit sample shows the formula, the exceptions, and the decision not to hide them. Keep a screenshot or PDF of the results with matched and REVIEW counts visible. In your explanation, name the lookup key, why exact matching was appropriate, how you aligned data types, and what you would ask a data owner about each unresolved record. This demonstrates control and judgment rather than memorizing a formula.
Quality gate
Check your own work
- Raw copy protected
- Unique key checked
- Exact match used
- Errors investigated
- Audit totals recorded