Lookups Mastery

Learning objectives

By the end you can:

1. Write XLOOKUP from memory with all six arguments, and explain why its exact-match default makes it structurally safer than VLOOKUP.
2. Use the if_not_found argument to replace fragile IFERROR wrappers and return meaningful "not found" values instead of #N/A.
3. Build an approximate-match lookup correctly against an ascending-sorted lower-bound table, and state precisely why the sort is not optional, the module's signature safety rule.
4. Compute a full progressive slab tax and a commission tier with a single lookup-driven formula, recovering both the marginal rate and the cumulative amount.
5. Write INDEX + MATCH and XMATCH, and name the three concrete situations where professionals still prefer them over XLOOKUP, left-lookups, robustness to inserted columns, and speed on very large sheets.
6. Perform two-dimensional lookups two ways: INDEX(matrix, MATCH(row), MATCH(col)) and XLOOKUP nested inside XLOOKUP.
7. Perform multi-key lookups with a Boolean-array key and with a concatenated helper key, and choose between them.
8. Reverse a lookup with search_mode -1 to fetch the most recent record, and shape a returned row or column with CHOOSECOLS, CHOOSEROWS, TAKE, and DROP.
9. Diagnose and prevent the four classic lookup failures, the VLOOKUP approximate-default disaster, hardcoded column indices, unsorted approximate matches, and unhandled #N/A.

Prerequisites & connections

Builds on. EX0.02 (Formulas I, relative/absolute/mixed references and the F4 toggle, IF/IFS logic, and IFERROR/IFNA, all of which you will now mostly replace with a cleaner argument), and EX1.01 (Formulas II, the SUMIFS/COUNTIFS aggregation family and the array-entering mindset; a lookup fetches one matching fact, an aggregation sums across many, and knowing which question you are asking is half of choosing the right function). You also need the Table discipline from EX0.01: every reference table in this module should be a real Excel Table (Ctrl+T), because structured references make lookups readable and immune to row growth.

Feeds forward. EX1.03 (Dynamic Arrays & LAMBDA) turns the single-cell lookups here into spilling report blocks: XLOOKUP can return a whole array, and FILTER generalises the multi-key lookup you meet in this module. EX2.01 (Data Tools & Integrity) uses lookups behind data-validation lists and formula-driven conditional formatting. On the analyst track, lookups are the connective tissue of every model: M2.05's benchmarking template pulls peer figures by ticker; M3.08's three-statement model looks up assumptions by scenario; the entire "one edit ripples correctly" property of a professional model (EX7.01) rests on never hardcoding what a lookup should fetch. When you meet the DCF sensitivity tables of EX2.02, the row/column intersection you learn here is the same machinery a two-variable Data Table automates.

Why VLOOKUP retired: meet XLOOKUP

First principles. A lookup answers a question of the form "in that reference range, find the row where the key matches mine, and hand me back the value sitting in a particular column." For two decades the tool for this was VLOOKUP, and it carried three design scars that cost analysts real money.

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) fails in three structural ways. First, it can only look rightward: the key must be the leftmost column of table_array, and the value returned must sit to its right. Want the product name to the left of a SKU column? VLOOKUP simply cannot. Second, the return column is a hardcoded integer, col_index_num is a number like 4, meaning "the 4th column". Insert a new column anywhere inside the table and every VLOOKUP pointing past it now returns the wrong column, silently, with no error. Third, and most dangerous, its fourth argument defaults to approximate match. Omit range_lookup and Excel assumes TRUE, approximate, which on unsorted data returns confident nonsense. We devote a whole common-mistake to this below; it is the deadliest default in the application.

XLOOKUP repairs all three. The signature is:

`` XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) ``

• lookup_value, the key you are matching.
• lookup_array, the single column (or row) you search in.
• return_array, the single column (or row) you fetch from. Because the search range and the return range are separate arguments, the return can sit anywhere, left, right, on another sheet, and inserting columns between them breaks nothing.
• if_not_found, what to return when there is no match, replacing the IFERROR wrapper.
• match_mode, 0 exact (the default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard.
• search_mode, 1 first-to-last (default), -1 last-to-first (reverse), 2/-2 binary search on sorted data.