Excel Advanced
Intermediate 14 min read Excel Advanced

Lookup and Reference Functions

Joining tables without opening a database

VLOOKUP, INDEX/MATCH, XLOOKUP and the new dynamic-array lookups let one table be enriched from another. XLOOKUP is the modern default; INDEX/MATCH still wins for right-side and two-way lookups.

VLOOKUPXLOOKUPINDEXMATCHreference functionsjoins
Prerequisites: Excel Essential Skills
Before we start

What you will learn

  • Compare VLOOKUP, INDEX/MATCH and XLOOKUP behavior
  • Handle missing keys and duplicate match keys correctly
  • Build two-way lookups using MATCH for rows and columns
Introduction

What is this?

Lookup functions find a value in another table by a key and return a related field. XLOOKUP(lookup, lookup_array, return_array) is the modern recommendation: handles left and right of the key, returns a default for missing keys, and supports wildcard and reverse matches. INDEX/MATCH composes array-style lookups and two-way table lookups that XLOOKUP alone can't do in one call.

Motivation

Why do you need it?

  • Enriching raw data with attributes (price lists, segments, country names) is daily analyst work.
  • Excel is where much ad-hoc joining happens before it deserves a database.
  • Choosing the right lookup avoids the classic VLOOKUP 'right side only' trap.
  • Missing-key handling decides whether your join silently hides rows or shows them.
Think about it

Mental model

Imagine

Looking up a word in a dictionary

Think of

A spreadsheet lookup

VLOOKUP is a dictionary that only lets you search the first column on the left page; XLOOKUP is a dictionary that lets you search any column and still read the definition beside or beyond it.

Mechanics

How it works

  • VLOOKUP key must sit in the table's leftmost column; returns a column to its right.
  • INDEX(range, row, col) fetches by position; MATCH(key, range) returns the position.
  • XLOOKUP handles missing keys with IF_NOT_FOUND and matches across the entire array.
  • Two-way lookups use MATCH twice — once for row, once for column — inside INDEX.
  • TRUE/fuzzy matches need sorted keys; FALSE/exact is the safe default for scrambled data.

XLOOKUP vs INDEX/MATCH.

Key
XLOOKUP
INDEX/MATCH
Result
Hands-on

Examples

Choosing the right lookup

Choosing the right lookup
// Modern: direction-independent, missing-key friendly
=XLOOKUP(A2, Dim_User[user_id], Dim_User[segment], "unknown")
 
// Two-way: retrieve the value at (rowMatch, colMatch)
=INDEX(Prices, MATCH(B2, Products, 0), MATCH(C1, Regions, 0))
 
// Legacy VLOOKUP (watch: 4th arg FALSE for exact match)
=VLOOKUP(A2, Dim_User, 3, FALSE)

XLOOKUP is readable and returns 'unknown' instead of errors. INDEX/MATCH handles two-way tables and right-side returns natively, and works in older Excel versions.

Real world

In production

  • Price enrichments: order lines join the current price list to compute expected revenue.
  • Segment joins: customer IDs pull a segment label for analysis groupings.
  • Two-way region × month tables feed budget-to-actual comparisons.
Guidelines

Best practices

  • Use XLOOKUP with an IF_NOT_FOUND default so missing keys surface as data, not #N/A.
  • Keep lookup tables on their own sheet and name them to make formulas self-documenting.
  • Verify unique keys: duplicate keys in a lookup table produce arbitrary first-match results.
  • Prefient real joins in Power Query when the relationship is permanent and bigger.
Watch out

Common mistakes

  • Using VLOOKUP's default approximate match with unsorted keys — silent wrong answers.
  • Leaving #N/A values to poison later SUMIFS and pivots.
  • Two lookups that disagree because key columns mix text and numbers (1 vs '1').
  • Handling one key but needing multi-key enrichment, then nesting lookups into a mess.
When things break

Debugging tips

  • #N/A everywhere? Check key type mismatch and key case, then the lookup column index.
  • Unexpected values? Confirm no duplicates in the lookup key column.
  • Two-way lookup off-by-one? MATCH with 0 returns exact position; wrap with data validation.
  • Regex the key with TRIM and VALUE to normalize types before lookup.
Interviews

Interview checkpoint

Practice

Hands-on lab

Enrich Order Data with Lookups
25 min · intermediate
In progress
Objectives
  • Join order lines to a product and customer table
  • Handle missing keys with IF_NOT_FOUND
  • Verify enrichment cell by cell

Enrich Order Data with Lookups

25 min · intermediate

0/5 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

Which lookup returns a value to the LEFT of the lookup key without restructuring the table?

2

Duplicates in the key column of a lookup table are harmless.

Go deeper

Official documentation

Microsoft — XLOOKUP
docs
Microsoft — INDEX function
docs