Match two small tables by a stable ID, identify missing records, and document the checks instead of hiding errors.
Created by the First Role Skills Editorial Team. This lesson uses a fictional workplace scenario and links to primary product documentation where the interface or feature matters.
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.
DeliverableAn audit column that returns the expected owner or a visible review flag for unmatched IDs.
Three beginner mistakes
- Matching on names instead of stable IDs
- Replacing every #N/A with a blank
- Using approximate match on unsorted business data
Complete it in six controlled steps
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.
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.
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.
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.
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.
Summarize the audit
Count matched, unmatched, blank, and duplicate keys. Add a note describing the source tables, formula, review date, and unresolved exceptions.
Before and after
Before:
Ticket 1042 -> copied owner name
Ticket 1043 -> blank
No record of the source or review decision
After:
=XLOOKUP(A2,Owners!A:A,Owners!B:B,”REVIEW”)
Matched: 8 | Review: 2 | Duplicate keys: 0
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.
Download the lookup audit practice file — A compact CSV with request and reference rows plus deliberate exceptions. — Download file
Use only fictional or fully anonymized data. Never upload an employer’s confidential information to a portfolio.
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.
Check your own work
- Raw copy protected
- Unique key checked
- Exact match used
- Errors investigated
- Audit totals recorded
Primary reference: Microsoft Support: XLOOKUP function
Why this matters in your first job
Many entry-level tasks involve two lists that should match but do not: customer lists and order exports, employee rosters and training records, invoices and payments. Lookup functions let you compare them quickly and find what is missing or inconsistent. The audit mindset in this guide, checking rather than assuming, is what managers value most when they hand you data.
Common questions
Which should I learn first, XLOOKUP or VLOOKUP? If your version of Excel or Sheets supports XLOOKUP, it is generally easier and more flexible. Still learn VLOOKUP, because you will meet it in older files.
Why does my lookup return errors for values that look identical? Hidden spaces, different capitalization in some systems, or numbers stored as text are common causes. Clean the data first; our guide to cleaning a messy contact list shows how.
How do I explain my audit to my manager? Summarize what you compared, how many records matched, what did not and what needs a decision. See problem-solving at work.