Advanced Lookups & Data Validation

Excel Fundamentals Chapter 5 used XLOOKUP for the simplest possible case: one key, one exact match, one answer. Real spreadsheets often need more — finding a value at the intersection of two keys, matching against a tier rather than an exact value, or restricting what a user is even allowed to type into a cell in the first place. This chapter covers all three, and closes with dropdown lists that stay in sync with live data automatically, using Chapter 1's own dynamic arrays.

Nested XLOOKUP: Looking Up in Two Dimensions

Imagine a grid with Regions down the side and Months across the top, each intersection holding that region's sales for that month. Finding "North region, March" needs two lookups at once — one to find which row, one to find which column — and XLOOKUP can nest inside itself to do both in one formula:

Read this from the inside out, the same way Chapter 1 taught you to read nested dynamic arrays: the inner XLOOKUP resolves first, turning "March" into the single column of sales figures for March. The outer XLOOKUP then searches that one column (not the whole grid) for the row matching "North," landing on exactly one cell — the intersection of both keys.

Approximate-Match XLOOKUP for Tiered Lookups

Excel Fundamentals Chapter 4 built a 4-band nested IF chain to sort a value into a grading band. For a genuinely long list of tiers (a commission structure, a tax bracket table), a long nested IF chain becomes unwieldy. XLOOKUP's optional 5th argument, match mode, offers a cleaner alternative:

This single formula replaces what would otherwise be a nested IF chain with one entry per tier — and unlike that nested chain, adding a new tier here means adding one row to the reference table, not editing the formula's own logic at all.

Data Validation: Basic Dropdown Lists

Data → Data Validation, with "Allow" set to List, restricts what can be typed into a cell to a fixed set of choices, shown as a dropdown arrow next to the cell. The source can be a typed, comma-separated list, or — far more maintainable — a reference to a range:

• Typed directly: Bronze,Silver,Gold,Platinum
• Referencing a range: point the Source field at Budget[Category] (Chapter 7's structured reference) — the dropdown then automatically includes any new category added to the Budget table, with zero maintenance on the dropdown itself.

Dependent Dropdowns: A Second List That Reacts to the First

A dependent dropdown is a second Data Validation list whose available choices change based on what's selected in a different cell — a Subcategory list that only shows subcategories belonging to whichever Category was picked. Chapter 1's FILTER makes this straightforward: set the second dropdown's Source to a formula that filters the full subcategory list down to just the ones matching the first dropdown's current selection.

Hands-On Exercises