Why this chapter matters
Business analysis often depends on matching IDs, categories, or dates across separate source files.
What you will learn
- Use XLOOKUP for exact and approximate matches with clear fallback behavior.
- Apply INDEX and MATCH when flexible lookup direction is needed.
- Detect and diagnose missing keys and duplicate key problems.
Lessons in this chapter
- Exact-key joinsMap dimension fields into fact tables with robust key checks. Read the full guide →
- Two-way lookupsRetrieve values by both row and column criteria.
- Approximate lookupsUse sorted thresholds for tiered pricing or scoring.
- Lookup QAAudit unmatched rows and duplicate keys before publishing results.
Study task
Merge product margin targets into a sales table by product ID, then produce an exceptions list for unmatched IDs.
Chapter checkpoint
What is the main advantage of XLOOKUP over legacy VLOOKUP for many business models?
XLOOKUP can return matches from either direction and supports built-in not-found handling, which simplifies robust models.