Lookups Mastery

Excel Mastery Track · Yellow belt · ~6–8 hours · Microsoft 365 on Windows assumed

A lookup is the single most-used act in an analyst's spreadsheet life: given this key, fetch that fact from somewhere else. Given a SKU, fetch its price. Given an income, fetch its tax band. Given a customer and a region, fetch the agreed rate. Every model, every reconciliation, every dashboard is, underneath, a mesh of lookups pulling facts from reference tables into the place a calculation needs them. Do lookups well and your workbook becomes a living system where one edit to a price list ripples correctly through a thousand cells. Do them badly (with a hardcoded number here, a fragile VLOOKUP there) and you have built a silent liar that returns a plausible wrong answer at the worst possible moment.

You're going to get fluent in the modern lookup stack. You'll retire VLOOKUP and make XLOOKUP your default. You'll learn the one place where approximate matching is the whole point and nothing like a bug: tax slabs and commission tiers. You'll keep INDEX + MATCH in your pocket for the jobs where professionals still reach for it. And you'll finish by shaping the results a lookup returns with CHOOSECOLS, TAKE, and DROP. One habit runs through all of it, and it's what separates analysts from spreadsheet tourists. Use exact match by default, use approximate match only on data you've sorted on purpose, and never type a magic number where a lookup belongs.

One version note, and we'll only say it once. Everything here assumes Microsoft 365. XLOOKUP and XMATCH don't exist in Excel 2019 or earlier. They arrived in Excel 2021, and CHOOSECOLS, TAKE, and DROP didn't turn up before Excel 2024. On older versions you fall back to INDEX + MATCH. So the back half of these notes treats that pairing as a skill you'll still use at work, and it's no museum piece.

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 reasons professionals keep them: left-lookups and robustness to inserted columns (their edge over VLOOKUP, which XLOOKUP shares), and speed on very large sheets (their edge over XLOOKUP, alongside running on Excel 2019 and earlier).
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) gave you relative/absolute/mixed references and the F4 toggle, IF/IFS logic, and IFERROR/IFNA, all of which you'll now mostly replace with a cleaner argument. EX1.01 (Formulas II) gave you the SUMIFS/COUNTIFS aggregation family and the array-entering mindset. A lookup fetches one matching fact and an aggregation sums across many. Knowing which question you're asking is half of choosing the right function, and the common mistakes below finish the job EX1.01 started. You also need the Table habit from EX0.01. Every reference table you build here 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'll meet below. You'll lean on the result-shaping functions all the time once EX1.03 makes spilling arrays second nature. Pass the mastery check below and EX1.03 opens up. 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, and M3.08's three-statement model looks up assumptions by scenario. The whole "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.

Core teaching content

XLOOKUP exact match and if_not_found

Exact match is the default, and it's the one you'll use most. The part worth slowing down for is what happens on a miss. A lookup that returns the wrong thing without a word is far more dangerous than one that shouts.

The bare =XLOOKUP(F2, A2:A50, C2:C50) returns #N/A when F2 is absent. #N/A means "not available", and it's telling you the truth. But it's ugly in a report, and it poisons any SUM that includes the cell. The old fix was to wrap the whole thing: =IFERROR(XLOOKUP(...), "Not found"). That works, but it's a blunt instrument. IFERROR swallows every error type, including a real #REF! from a deleted range or a #VALUE! from a type mismatch, and it dresses real bugs up as "Not found".

XLOOKUP gives you a sharper tool. Its fourth argument, if_not_found, fires only on a no-match and leaves true errors visible.

=XLOOKUP(F2, A2:A50, C2:C50, "SKU not in catalogue")

Now a missing SKU returns a readable string, and a broken formula still errors loudly. Make that your habit. Use if_not_found for the expected absence, a code that might legitimately not be there, and let real errors surface. If you need a numeric fallback so downstream sums keep working, return 0 explicitly with =XLOOKUP(F2, A2:A50, C2:C50, 0). Then the zero is a choice you made on purpose, and nobody can mistake it for an accident.

UI and keyboard. Type the function directly. Analysts almost never use the mouse for this. If you want the argument prompts, press Shift+F3 (Insert Function) with the cursor in the cell, or just type =XLOOKUP( and read the in-cell ScreenTip that lists each argument as you go. To audit a finished lookup, select the cell and press F2 to see its ranges highlighted in colour, or use Formulas ▸ Evaluate Formula to step through it. Select a sub-expression in the formula bar and press F9 to see what that piece evaluates to. Press Esc afterwards so you don't commit the change.

Multi-key lookups

Sometimes no single column is the key. What identifies a row is a combination. A price depends on product and region. A rate depends on customer and currency, and a balance on account and period. There are two dependable techniques here, and you'll want both.

Take a "tidy" (long-form) price list where each product-region pair is its own row:

     A          B         C
1    Product    Region    Price
2    Widget     East      12.50
3    Widget     West      13.00
4    Gadget     East      8.20
5    Gadget     West      8.50

Technique 1, the Boolean-array key (no helper column). Multiply the equality tests together. (A2:A5="Gadget") yields {FALSE;FALSE;TRUE;TRUE}, and (B2:B5="West") yields {FALSE;TRUE;FALSE;TRUE}. Their product is {0;0;0;1}, with a 1 only where both conditions hold. Then look up the 1:

=XLOOKUP(1, (A2:A5="Gadget") * (B2:B5="West"), C2:C5)

This returns 8.50. It's compact, needs no extra column, and stretches to three or four keys if you multiply in more conditions. One caution, though. If more than one row matches, it returns the first, so make sure your key combination is unique.

Technique 2, the concatenated helper key. Build a helper column D2 =A2&"|"&B2 giving Widget|East, Widget|West, and so on, then look up the joined key:

=XLOOKUP(H2 & "|" & H3, D2:D5, C2:C5)

The | separator is there on purpose, because it prevents false joins. Without a separator, "12"&"3" and "1"&"23" both become "123". The helper key is easier for a colleague to audit, and you can reuse the same key for several lookups. It costs you one extra column. Pick the Boolean array when you want zero helper columns, and the concatenation when auditability matters more than tidiness.

Shaping results: CHOOSECOLS, TAKE, DROP

A lookup doesn't have to return a single cell. XLOOKUP can hand back an entire row or column, and a family of dynamic-array functions then shapes that result without helper columns. They pick, trim and reorder it. Take a customer Table:

     A         B        C          D          E
1    CustID    Name     City       Segment    CreditLimit
2    C001      Acme     Pune       Gold       500000
3    C002      Beta     Mumbai     Silver     250000
4    C003      Gamma    Delhi      Gold       750000
5    C004      Delta    Chennai    Bronze     100000

Return the whole record for a customer by giving XLOOKUP a multi-column return array:

=XLOOKUP("C003", A2:A5, B2:E5)

This spills {Gamma, Delhi, Gold, 750000} across four cells. Now shape it:

• CHOOSECOLS(array, col1, col2, …) picks columns by position. =CHOOSECOLS(XLOOKUP("C003",A2:A5,B2:E5), 1, 4) returns just {Gamma, 750000}. That's the name and the credit limit, with the middle two dropped.
• TAKE(array, rows, [cols]) keeps from the edges. =TAKE(XLOOKUP("C003",A2:A5,B2:E5), , 2) keeps the first 2 columns, which gives {Gamma, Delhi}. A negative count takes from the end, so TAKE(array, , -1) keeps the last column.
• DROP(array, rows, [cols]) discards from the edges. =DROP(XLOOKUP("C003",A2:A5,B2:E5), , 1) drops the first column and leaves {Delhi, Gold, 750000}.
• CHOOSEROWS(array, row1, …) is the row-wise sibling. It's useful when a lookup or FILTER returns many rows and you want specific ones. TAKE(array, 3) keeps the top 3 rows, and TAKE(array, -2) the bottom 2.

These pieces snap together. A lookup fetches a block, and CHOOSECOLS/TAKE/DROP carve out the shape your report needs. The result stays live, so when the source data changes, the carved result updates itself. Once spilling arrays feel natural, you'll reach for this all the time.

Common mistakes & how experts think differently

1. The VLOOKUP range_lookup = TRUE default disaster, the signature warning for lookups. VLOOKUP's fourth argument defaults to TRUE (approximate match). Leave it out, as beginners always do, and on unsorted data Excel runs a binary search that assumes an order that isn't there. It hands back a wrong value from an essentially arbitrary row and doesn't say a word. You get no error and no warning, and the confident wrong number flows into every total downstream. The beginner types =VLOOKUP(key, table, 4) and trusts it. The expert's reflex is visceral. An approximate match is only ever valid on a deliberately ascending-sorted lower-bound table, and for every exact-match job the fourth argument must be FALSE. Better still, the whole function should be XLOOKUP, whose exact-match default removes the trap at the root. If you inherit a workbook full of bare VLOOKUPs, treat each one as guilty until you've checked its fourth argument.
2. Hardcoded column indices that break on a column insert. =VLOOKUP(key, A:H, 6, FALSE) returns column 6, until someone inserts a column. After that, "6" points somewhere new and every result is wrong, with nothing to flag it. The expert never counts columns. XLOOKUP names its return range directly, and INDEX + MATCH refers to whole columns that move with the data. If you must keep a VLOOKUP, at least make the index dynamic with MATCH on the header (MATCH("Price", A1:H1, 0)) so it heals itself. The real fix, though, is to stop using functions that count.
3. Forgetting that XLOOKUP is exact by default (good) while VLOOKUP is approximate by default (bad). This flip trips people up when they move between the two. With XLOOKUP you opt in to approximate matching by typing match_mode -1 or 1. With VLOOKUP you had to opt in to safety by typing FALSE. Experts get the direction into their bones. Reaching for approximate is a conscious, sort-first decision, never a default they fell into.
4. Approximate match on unsorted data. Even in the legitimate banding case, MATCH(…, 1) and VLOOKUP(…, TRUE) need the lookup column sorted ascending. A single out-of-order row makes them return the wrong band with no error. The expert sorts the band table ascending, once, and often adds a check cell that confirms the bounds are monotonic. XLOOKUP -1 scans, so it tolerates disorder. Leaning on that is a bad habit, though. Sort anyway, so the intent is visible and binary search stays available.
5. Leaving #N/A unhandled. A lookup that might legitimately miss should say so in words people can act on. Beginners either ignore the #N/A, which then breaks every SUM that touches the cell, or bury it under a blanket IFERROR that also hides real bugs. The expert uses XLOOKUP's if_not_found argument. It fires only on a real no-match and lets #REF!/#VALUE! surface. That's how you tell "this key isn't in the catalogue" (expected, handle it) apart from "this formula is broken" (fix it).
6. Using a lookup where an aggregation is meant (and vice versa). A lookup fetches one matching fact. If a key appears many times and you want the total, you need SUMIFS. XLOOKUP would hand you only the first match and give no sign that the others exist. The expert first asks "one fact or a sum across many?" and picks the family to suit. You met that distinction with the aggregation functions, and lookups finish the job.
7. Approximate-matching text or IDs that only look numeric. Approximate match is for continuous, ordered quantities. Apply it to account codes or SKUs, which have no meaningful "next smaller", and you get plausible-looking cross-contamination. Exact match, the default, is the only correct mode for identifiers.

So what ties all seven together? An expert's default is exact, and every departure from it is a deliberate, sorted, documented decision. Beginners inherit whichever default the function shipped with. Professionals impose their own.

Worked examples

Worked example 1: India income-tax slabs: approximate-match banding (India)

The task. Under India's new tax regime for FY2024-25, build a lookup that returns both the marginal rate and the full progressive tax for any income, and run it for four sample incomes: ₹6,50,000, ₹9,00,000, ₹13,50,000, and ₹22,00,000. We compute the statutory slab tax before the 4% health-and-education cess and before any Section 87A rebate. Both get a note at the end as real-world wrinkles, because the lookup mechanics are the lesson here.

The slab structure (new regime, FY2024-25): nil up to ₹3,00,000; 5% on ₹3–7 lakh; 10% on ₹7–10 lakh; 15% on ₹10–12 lakh; 20% on ₹12–15 lakh; 30% above ₹15,00,000.

Step 1: build the ascending lower-bound table. The lookup column must be the band floors, sorted ascending, with the marginal rate and the cumulative tax accrued at each floor:

     A              B          C
1    Lower bound    Rate       Cumulative tax at bound
2    0              0%         0
3    300000         5%         0
4    700000         10%        20000
5    1000000        15%        50000
6    1200000        20%        80000
7    1500000        30%        140000

Check column C yourself from first principles, band by band. Below ₹3,00,000 nothing is taxed, so the floor at ₹3,00,000 carries ₹0 accrued. Cross ₹7,00,000 and you've paid 5% on the full ₹3 to 7 lakh band. That's 0.05 × (7,00,000 − 3,00,000) = 0.05 × 4,00,000 = ₹20,000. Cross ₹10,00,000 and you add 10% × (10,00,000 − 7,00,000) = 0.10 × 3,00,000 = ₹30,000, which gives 20,000 + 30,000 = ₹50,000. Crossing ₹12,00,000 adds 15% × 2,00,000 = ₹30,000 and takes you to ₹80,000. Crossing ₹15,00,000 adds 20% × 3,00,000 = ₹60,000, for ₹1,40,000. Those match the column-C entries one for one.

Step 2, the marginal-rate lookup. For an income in E2, the band's marginal rate is:

=XLOOKUP(E2, A2:A7, B2:B7, , -1)          (or:  =INDEX(B2:B7, MATCH(E2, A2:A7, 1)) )

match_mode -1, "exact or next smaller", lands on the largest floor ≤ the income. That's the band the income falls in.

Step 3, the full progressive tax. Cumulative-at-floor plus marginal rate times the income above the floor:

=XLOOKUP(E2,A2:A7,C2:C7,,-1) + XLOOKUP(E2,A2:A7,B2:B7,,-1) * (E2 - XLOOKUP(E2,A2:A7,A2:A7,,-1))

Step 4, run the four incomes (all figures recomputed and verified):

Step 5, read it. The same three formulas handle every income. Change a rate or a slab in the table and all four results update, and you never rewrite a formula. So tax engines, tariff calculators, and commission systems are all built as lower-bound lookup tables, and not as nested IFs. A six-band nested IF is unreadable and impossible to audit. This is two columns and one formula.

Real-world wrinkles, stated here but deliberately kept outside the lookup lesson. A 4% health-and-education cess is added on top, so ₹40,000 becomes ₹41,600 and ₹3,50,000 becomes ₹3,64,000. A Section 87A rebate also zeroes the actual liability for taxable income up to ₹7,00,000 in the new regime. So the ₹6,50,000 case's payable tax is nil, even though its slab tax is ₹17,500. Those are FY2024-25 figures only. From FY2025-26 the new regime has seven bands (nil to ₹4 lakh, then 5% steps every ₹4 lakh to 30% above ₹24 lakh). The rebate then makes tax nil up to ₹12 lakh of taxable income, and that carries into the Income-tax Act, 2025 from tax year 2026-27 (as of October 2026). The same lookup handles the new table with one more row. Model the mechanical slab first. Layer the cess and the rebate on afterwards as separate, clearly labelled steps.

Worked example 2: US regional price matrix: two-dimensional lookup (US)

The task. A US distributor prices four products differently across four regions. Given an order of four line items, fetch each unit price from the price matrix using both INDEX + MATCH + MATCH and the XLOOKUP-in-XLOOKUP equivalent, then extend and total the order in dollars.

Step 1, the price matrix (unit prices, $):

      A          B       C       D         E
1     Product    East    West    Central   South
2     Widget     12.50   13.00   12.75     13.25
3     Gadget     8.20    8.50    8.35      8.60
4     Gizmo      21.00   21.75   21.25     22.00
5     Sprocket   5.40    5.60    5.50      5.75

Step 2, the order block. Lay the order out as its own Table so each line looks up its own price:

      G          H         I       J             K
1     Product    Region    Qty     Unit price    Ext cost
2     Widget     East      100     =lookup       =I2*J2
3     Gadget     West      250     =lookup       =I3*J3
4     Gizmo      Central   40      =lookup       =I4*J4
5     Sprocket   South     300     =lookup       =I5*J5

Step 3, the unit-price formula, two ways. Here's the INDEX + MATCH + MATCH form for line 2. Note the absolute $ on the matrix, which lets you copy it down:

J2 =INDEX($B$2:$E$5, MATCH(G2, $A$2:$A$5, 0), MATCH(H2, $B$1:$E$1, 0))

Take Widget in the East. MATCH(G2, $A$2:$A$5, 0) finds the product row, and Widget is row 1. MATCH(H2, $B$1:$E$1, 0) finds the region column, and East is column 1. INDEX reads the cell where they cross, which is 12.50. The nested XLOOKUP version returns the same value:

J2 =XLOOKUP(G2, $A$2:$A$5, XLOOKUP(H2, $B$1:$E$1, $B$2:$E$5))

The inner XLOOKUP returns the whole East column, {12.50; 8.20; 21.00; 5.40}. The outer one picks the Widget row from it, and you get 12.50 again.

Step 4, extend and total. Put K2 =I2*J2 in and copy it down, then total with =SUM(K2:K5). Every figure has been recomputed and verified:

Check the total longhand. 1,250 + 2,125 = 3,375. Add 850 and you're at 4,225. Add 1,725 and you reach $5,950.00.

Step 5, read it. Nobody typed a single price into the order block. Every unit price is fetched from the matrix. Correct a price in B2:E5 and the order re-prices and re-totals instantly. That's what lookup-driven design promises, and the mini-project asks you to build it end to end. The 2-D lookup also survives reordering. Shuffle the region columns or the product rows and both formulas still find the right cell, because they match on labels. A hardcoded VLOOKUP column index matches on a fixed position, so it throws that protection away.

Practice set

Work each problem before you read its solution. Assume every reference range is a real Table where that makes sense. Formulas are given in full, and where two idioms exist, you'll see both.

P1 (guided). Exact match with if_not_found. A price list has product codes in A2:A20 and unit prices in C2:C20. Write the formula that returns the price for the code in F2, and returns the text Code not found instead of #N/A when the code is absent. Then explain why this is safer than =IFERROR(XLOOKUP(F2,A2:A20,C2:C20),"Code not found").

Solution. =XLOOKUP(F2, A2:A20, C2:C20, "Code not found"). The fourth argument, if_not_found, fires only on a real no-match. The IFERROR version is more fragile, because it also swallows real errors, such as a #REF! from a deleted column or a #VALUE! from a type mismatch. It relabels them "Code not found" and hides actual bugs. Use if_not_found for expected absences, and let true errors stay visible.

P2 (guided). Left-lookup. A table has product names in A2:A10, prices in B2:B10, and SKUs in C2:C10. Given the SKU VLV-02 in F2, return the product name, which sits to the left of the SKU column. Show both the XLOOKUP and INDEX + MATCH forms, and say why VLOOKUP can't do this.

Solution. =XLOOKUP(F2, C2:C10, A2:A10) or =INDEX(A2:A10, MATCH(F2, C2:C10, 0)). Both search the SKU column and return from the name column, which lies to its left. VLOOKUP can't, because it needs the key in the leftmost column of its table and can only return columns to the right of the key. Keeping the search range and the return range separate is what makes left-lookups trivial for the two modern idioms.

P3 (guided). Progressive slab tax. Using the FY2024-25 new-regime table from Worked example 1 in A2:C7, write the single formula that returns the full progressive tax for an income in E2, then evaluate it for ₹13,50,000.

Solution. =XLOOKUP(E2,A2:A7,C2:C7,,-1)+XLOOKUP(E2,A2:A7,B2:B7,,-1)*(E2-XLOOKUP(E2,A2:A7,A2:A7,,-1)). Take ₹13,50,000. Its band floor is ₹12,00,000, with a 20% rate and ₹80,000 accrued, so tax = 80,000 + 0.20 × (13,50,000 − 12,00,000) = 80,000 + 0.20 × 1,50,000 = 80,000 + 30,000 = ₹1,10,000. The match_mode -1 lands on the largest floor not exceeding the income.

P4. Two-dimensional lookup. Using the price matrix from Worked example 2 (A1:E5, products down A2:A5, regions across B1:E1), write the formula that fetches the unit price for the product in H2 and region in H3, in both the INDEX/MATCH/MATCH and nested-XLOOKUP forms. Evaluate for Gizmo / Central.

Solution. =INDEX(B2:E5, MATCH(H2,A2:A5,0), MATCH(H3,B1:E1,0)) or =XLOOKUP(H2, A2:A5, XLOOKUP(H3, B1:E1, B2:E5)). Gizmo is row 3 of the body and Central is column 3, so both return $21.25. The INDEX form matches row and column separately. The nested XLOOKUP returns the Central column first, then picks the Gizmo row from it.

P5. Commission tiers. A monthly commission schedule pays 0% below ₹2,00,000 of sales, 2% from ₹2–5 lakh, 4% from ₹5–10 lakh, 6% from ₹10–20 lakh, and 8% above ₹20,00,000. Build the lower-bound rate table, write the marginal-rate lookup, and compute the commission (rate × sales, flat on total sales, not progressive) for monthly sales of ₹12,50,000.

Solution. Put the ascending lower-bound table, 0 → 0%, 200000 → 2%, 500000 → 4%, 1000000 → 6%, 2000000 → 8%, in, say, A2:B6. The rate lookup is =XLOOKUP(D2, A2:A6, B2:B6, , -1), or =INDEX(B2:B6, MATCH(D2, A2:A6, 1)). Sales of ₹12,50,000 fall in the ₹10 to 20 lakh band, so the rate is 6% and the commission is 0.06 × 12,50,000 = ₹75,000. Notice that this schedule is flat-on-total, a "cliff", unlike the progressive tax. The band gives one rate, and it applies to all sales. That's a design choice of the commission plan, so read the plan's wording to know which kind you have. It matters as much as the formula.

P6. Multi-key lookup. From the tidy price list Product (A2:A5), Region (B2:B5), Price (C2:C5): rows Widget/East 12.50, Widget/West 13.00, Gadget/East 8.20, Gadget/West 8.50, return the price for Gadget in West, using the Boolean-array method and the concatenated-key method.

Solution. With the Boolean array, =XLOOKUP(1, (A2:A5="Gadget")*(B2:B5="West"), C2:C5), the two tests multiply to {0;0;0;1}. The lookup finds the 1 on the fourth row and returns 8.50. For the concatenated key, build D2 =A2&"|"&B2 down the column, then =XLOOKUP("Gadget|West", D2:D5, C2:C5) also returns 8.50. The Boolean method needs no helper column, and the concatenated one is easier to audit. Both assume the key combination is unique. If it isn't, both return the first match.

P7. Reverse search for the most recent record. A price-change log is sorted oldest-to-newest: dates in A2:A6, product in B2:B6, price in C2:C6, with three Widget rows at prices 100, 105, then 110 (most recent). Return the current (latest) Widget price with one lookup.

Solution. =XLOOKUP("Widget", B2:B6, C2:C6, , 0, -1). The sixth argument, search_mode -1, scans last-to-first, so the first Widget it meets is the most recent row, and you get 110. A default XLOOKUP scans first-to-last and would return the oldest Widget price, 100. That's the classic error with append-only logs. Reverse search is the idiom for "give me the latest" against a chronological table.

P8. Insert-proof return column with XMATCH. You must fetch a value from a wide table where the target column's position may change as columns are added. Headers are in A1:H1 and data in A2:H100. You want the value from the column headed Margin for the key in K2, and the key column is A. Write a formula that survives column inserts.

Solution. =INDEX($A$2:$H$100, XMATCH(K2, $A$2:$A$100), XMATCH("Margin", $A$1:$H$1)). XMATCH(K2, …) finds the key's row, and XMATCH("Margin", $A$1:$H$1) finds the Margin column by its header. There's no counted index anywhere. Insert a column before Margin and the header XMATCH simply finds it again, so nothing breaks. A VLOOKUP with a hardcoded col_index_num would return the wrong column after any such insert, and it wouldn't warn you. Matching the header by name is the fix.

P9. Shaping a returned record. Using the customer Table (CustID A2:A5, then Name, City, Segment, CreditLimit in B2:E5), return only the Name and CreditLimit for customer C003 as a two-cell spilled result, using XLOOKUP plus a shaping function.

Solution. =CHOOSECOLS(XLOOKUP("C003", A2:A5, B2:E5), 1, 4). The XLOOKUP returns the whole record, {Gamma, Delhi, Gold, 750000}, and CHOOSECOLS(…, 1, 4) keeps the 1st and 4th columns, {Gamma, 750000}. The equivalent =DROP(TAKE(XLOOKUP("C003",A2:A5,B2:E5),,4),,) is clumsier. When you want specific, non-adjacent columns, CHOOSECOLS is the direct tool.

P10. The VLOOKUP approximate-default disaster. A bonus table sorted ascending by employee ID holds 1001 → 5000, 1002 → 6000, 1005 → 9000, 1009 → 12000 in A2:B5. An analyst wants the bonus for employee 1003, a new hire not yet in the table, and writes =VLOOKUP(1003, A2:B5, 2), leaving out the fourth argument. What does it return, why is that dangerous, and what should they have written?

Solution. With the fourth argument left out, VLOOKUP uses approximate match (TRUE). It finds the largest ID ≤ 1003. That's 1002, so it returns that person's bonus, ₹6,000. Nothing on the sheet looks wrong. Yet employee 1003 has no bonus record, and the right behaviour is "not found". What came back instead is someone else's number. The fix is exact match. =VLOOKUP(1003, A2:B5, 2, FALSE) returns #N/A, which is correct. Better still, =XLOOKUP(1003, A2:A5, B2:B5, "No bonus record") uses its exact-match default to catch the miss and returns a meaningful message. That one missing FALSE is the most expensive keystroke in spreadsheet history. Always match exactly, unless you deliberately built a sorted band table.

P11 (timed: 6 min). End-to-end line pricer. Using the US price matrix (A1:E5) from Worked example 2, price a two-line order: 80 units of Gadget in South, and 150 units of Widget in Central, and give the order total. Write the unit-price formula once (copyable) and the total.

Solution. The copyable unit price, with an absolute matrix, is =INDEX($B$2:$E$5, MATCH(product, $A$2:$A$5, 0), MATCH(region, $B$1:$E$1, 0)). Gadget/South is $8.60, so 80 × 8.60 = $688.00. Widget/Central is $12.75, so 150 × 12.75 = $1,912.50. Order total = 688.00 + 1,912.50 = $2,600.50. Every price is fetched, so the whole order re-prices if the matrix changes.

P12 (timed: 6 min). Choose the right tool. For each mini-task, name the single best function or idiom, and justify it in one line. (a) Fetch one product's price by exact code. (b) Total all sales for one region across many rows. (c) Fetch a rate from an ascending tier table. (d) Fetch a value at the intersection of a product row and month column. (e) Return the most recent status for an account from a chronological log.

Solution. (a) XLOOKUP exact, because it's one fact by unique key and exact match is the default. (b) SUMIFS. This is an aggregation across many rows, and XLOOKUP would wrongly return only the first match. (c) XLOOKUP with match_mode -1, or INDEX + MATCH with match_type 1, on the sorted lower-bound table. That's the legitimate approximate-match case. (d) INDEX(matrix, MATCH(row), MATCH(col)) or nested XLOOKUP, since it's a 2-D lookup. (e) XLOOKUP with search_mode -1, because a reverse scan returns the latest record. The skill underneath all five is asking yourself, before you type, whether you want one fact or a sum. Is it exact or banded? Is there one key or two?

Applied mini-project

Build a lookup-driven invoice pricer.

Assemble a small but complete pricing workbook that turns a raw order into a priced invoice using nothing but lookups. No number gets typed twice, and no price is hardcoded.

Set up three reference sheets (each a real Table, Ctrl+T):

1. Products: ProductCode, ProductName, BaseUnitPrice. At least 10 products.
2. RegionMatrix: a product × region grid of unit prices. If you prefer the tidy form, use a Product, Region, Price long table for a multi-key lookup instead. Cover 4 regions.
3. CustomerTiers: a customer table with CustomerID, CustomerName, and an assigned Segment. Add a small ascending lower-bound table that maps annual spend to a tier discount (e.g., 0 → 0%, 5,00,000 → 3%, 20,00,000 → 6%, 50,00,000 → 10%).

Build the invoice sheet:

1. An input block: CustomerID, and an order table of ProductCode, Region, Qty line items (at least 5 lines).
2. ProductName per line: XLOOKUP (or INDEX + MATCH) on the Products table (a left- or right-lookup as your layout demands).
3. UnitPrice per line, a 2-D lookup into RegionMatrix by product and region. Use both INDEX/MATCH/MATCH and the nested-XLOOKUP form on two lines, to prove they agree.
4. LineExt = Qty × UnitPrice, and a Subtotal = SUM(...).
5. A tier discount. Look up the customer's annual spend against the ascending discount table with an approximate match (match_mode -1 / match_type 1), apply it to the subtotal, and show Discount and NetTotal.
6. A robustness pass. Point every fetch at a code that does not exist and confirm your if_not_found messages fire with something readable, and that nothing sprays #N/A. Then insert a column into a reference Table and confirm nothing breaks. It won't, if you avoided hardcoded indices.

Rubric (self-score; 0 = missing/wrong, 1 = partial, 2 = correct and defended):

Pass: ≥ 8/10, with no zero on any criterion. Keep the workbook. Its RegionMatrix and tier logic become the pricing spine you'll reuse whenever a model needs assumptions fetched by key.

Lab: open the Lookups & aggregation database workbook lab in the Practice ▸ Excel tab and pass its checks.

Reading & resources

Function references (reach for these first):

• Microsoft Learn, the official XLOOKUP, INDEX, MATCH, XMATCH, CHOOSECOLS/TAKE/DROP function pages (search "XLOOKUP function Microsoft support"): authoritative argument definitions and edge-case notes. [Free] [Beginner]
• ExcelJet (exceljet.net), concise, example-driven entries for every lookup function, plus its "XLOOKUP vs VLOOKUP" and "INDEX and MATCH" explainers: the fastest way to see a formula in context. [Free] [Beginner]

Deeper practice and pattern libraries:

• Chandoo (chandoo.org), lookup tutorials and the classic "INDEX + MATCH" and "two-way lookup" write-ups, with downloadable practice files. [Free] [Beginner]
• Leila Gharani (YouTube), her XLOOKUP deep-dives and 2-D / multi-condition lookup videos are the best moving-picture treatment of everything covered here. [Free] [Beginner]
• Mynda Treacy / MyOnlineTrainingHub, lookup articles that emphasise the when-to-use-which judgment you've been practising. [Free] [Intermediate]

For the sceptic's habits (the common-mistakes section, expanded):

• Any reputable "why VLOOKUP is dangerous" article that walks the approximate-default trap, search "VLOOKUP TRUE approximate match error"; internalise the failure before you inherit a workbook full of them. [Free] [Beginner]

Bridge forward: the dynamic-array functions used lightly here (CHOOSECOLS, TAKE, DROP, and XLOOKUP returning arrays) are the whole subject of EX1.03. Skim its intro once you're comfortable spilling a returned record. The row/column intersection of the two-dimensional lookup comes back in EX2.02, where a two-variable Data Table automates it.

The Modern Analyst's Addendum

Everything above teaches this skill from first principles, by hand, that is how you learn it, and the mastery check still tests it that way. This addendum shows how a working analyst amplifies the same skill today. It adds; it never replaces. (R1/R10)

AI-Augment this skill

{
  "skill": "Choosing and building the correct lookup -- VLOOKUP retired in favour of XLOOKUP, INDEX+MATCH kept for specific jobs, approximate match reserved for deliberately sorted banding tables, and result-shaping with CHOOSECOLS/TAKE/DROP",
  "use": "Ask an assistant to rewrite a fragile VLOOKUP as XLOOKUP or INDEX+MATCH, then test the rewrite on a case where the OLD formula was already wrong -- unsorted data, an inserted column -- to confirm the rewrite actually fixes the bug and is not just a syntax facelift.",
  "tools": ["Microsoft Copilot in Excel (formula rewrite suggestions)", "a general-purpose LLM chat for explaining match-mode and search-mode arguments", "Excel's Evaluate Formula tool -- not AI, but the ground truth for what a lookup actually returned and why"],
  "prompt": "Here is a VLOOKUP formula I inherited: <PASTE>. Rewrite it as XLOOKUP with an explicit match mode, and tell me what its fourth argument was actually doing -- exact match, or approximate -- before the rewrite.",
  "verify": "Build one small test case with a KNOWN correct answer where the original formula was already wrong -- an unsorted band table, or a table with an inserted column -- and run the assistant's rewritten formula on it. A rewrite that only looks more modern but returns the same wrong answer has fixed nothing.",
  "diy": "You must be able to write INDEX+MATCH and XLOOKUP by hand, and explain without help why an approximate-match VLOOKUP on unsorted data can return a wrong answer with no error. The mastery gate is unaided."
}

Modern Data Analysis

By hand first. P5 built a commission-tier lower-bound table (₹0/2/5/10/20 lakh → 0/2/4/6/8%) and looked up 6% for sales of ₹12,50,000, giving a ₹75,000 commission. It came out right because the table was sorted ascending, which is the signature safety rule for every banded lookup.

Today's workflow. Common mistake #4 already says that a single out-of-order row makes approximate match return the wrong band with no error. Don't just repeat the warning. Build the broken table and watch it happen.

Tools & sources (IN + US). pandas/Python runs the constructed demonstration below. It mirrors the mechanism that Microsoft's own MATCH function reference documents plainly: match_type 1 "assumes that [the array is] sorted in ascending order." For India, the Income Tax Department / CBDT's published New Tax Regime slab notification is the actual source behind the slab table in the first worked example. For the US, the IRS's annually published Revenue Procedure tax-bracket tables (irs.gov) are the literal US analogue of the same ascending-lower-bound lookup shape, in a different currency and with a different band count.

# recomputed in-session — R3
# The module's own P5 commission-tier table, correct ascending order:
sorted_table = [(0, 0.00), (200000, 0.02), (500000, 0.04), (1000000, 0.06), (2000000, 0.08)]
# A realistic corruption: a mid-year promo tier appended at the bottom instead of re-sorted:
unsorted_table = sorted_table + [(300000, 0.03)]

def true_rate(key, table):
    return max((b, r) for b, r in table if b <= key)[1]

def binary_search_rate(key, table):
    # mirrors the documented mechanism: match_type 1 / VLOOKUP(TRUE) binary-searches
    # assuming ascending order -- see Microsoft's own MATCH function reference
    bounds = [b for b, _ in table]
    lo, hi, best = 0, len(bounds) - 1, -1
    while lo <= hi:
        mid = (lo + hi) // 2
        if bounds[mid] <= key:
            best, lo = mid, mid + 1
        else:
            hi = mid - 1
    return table[best][1] if best >= 0 else None

for key in [50000, 250000, 350000, 650000, 1250000, 2500000]:
    correct = true_rate(key, sorted_table)
    actual = binary_search_rate(key, unsorted_table)
    flag = "  <- WRONG" if actual != correct else ""
    print(f"sales={key:>8,d}  correct={correct:.0%}  approx-match-on-unsorted={actual:.0%}{flag}")

Verify. The script reproduces the first worked example's slab tax on all four of its incomes (₹17,500 on ₹6,50,000, ₹40,000 on ₹9,00,000, ₹1,10,000 on ₹13,50,000 and ₹3,50,000 on ₹22,00,000). It also matches the second worked example's order total of $5,950.00, all before it touches the constructed table. On the corrupted table, five of six test sales figures still return the correct rate. ₹25,00,000, though, returns 3% instead of the correct 8%. That's a wrong answer with no error attached, the very thing common mistake #4 warns about, and this time you've produced it yourself.

Quantitative lens

How much of the key-space actually breaks depends on where the out-of-order tier lands, and the relationship isn't simple. We tested every insertion position for one extra tier in the P5 table, across a dense grid of 2,201 candidate sales figures (₹0 to ₹22,00,000 in ₹1,000 steps). Inserting the new tier second corrupts 0.0% of the domain. Inserting it first or third corrupts 9.1%, and inserting it fourth corrupts 22.7%. Insert it fifth, just before the last tier, and it corrupts 68.2%. That's nearly seven in ten possible sales figures returning the wrong rate. Appending it at the very end, as in the demonstration table above, corrupts 9.1%.

You might expect corruption to get worse toward the middle. It doesn't. What the numbers show is that you can't predict the exposure without testing it. The binary search's failure depends on the specific bounds involved, and it doesn't move smoothly with position. The worst case (68.2%) is right next to a much smaller one (22.7%). Two different positions both come out at 9.1%, and that's a coincidence of these particular numbers. They aren't equally "central." So the old rule, sort ascending, always, comes back here as a number instead of a warning. No rule of thumb tells you how bad an unsorted table will be. You'd have to build it and check, and that defeats the point of trusting the lookup in the first place.

A word on limits. These fractions describe one five-tier commission table with one inserted tier. A table with different bound spacing or more tiers would give different numbers, so the specific percentages don't transfer to a table of a different shape. What does carry over is the qualitative finding: exposure is highly sensitive to where, and what, gets inserted. The dense, evenly spaced grid also treats every candidate sales figure as equally likely, and real sales data isn't like that. A table's actual risk depends on where real sales cluster, and a uniform grid can't tell you that.

Where this goes next: galaxy cross-links

• G2 · CS1.04 Algorithmic Thinking, Recursion & Big-O, the binary search that the Python model above reproduces is the same algorithm MATCH's approximate mode runs internally, and its ascending-order precondition is a textbook example of an algorithm's correctness depending on an assumption about its input.
• G2 · CS2.03 Databases & SQL for Analysts, a lookup is a single-row join by another name; SQL's JOIN is the industrial-strength version of XLOOKUP/INDEX+MATCH, built to stay correct at a scale no worksheet can hold.

Teach it back & journal

Feynman prompt. Explain to a shopkeeper who has never used Excel how a "lookup" works, using only the metaphor of a price list on the wall. When a customer names an item, the shopkeeper's eye runs down the item column, stops on the matching row, and reads across to the price. That's XLOOKUP. Then explain the one twist that trips everyone up. What happens if the customer names an item that isn't on the list? A careful shopkeeper wants the wall to say "not stocked" and never to quote the price of the item above it. Only in your last two sentences may you use the words exact match, approximate match, and if_not_found. Attach each one to the moment in the story it describes.

Journal prompt. Find one workbook you already rely on (a tracker, a budget, an invoice) and hunt it for two things: (1) any number that is typed in where it should have been fetched from a reference table (a price, a rate, a tax band), and (2) any VLOOKUP whose fourth argument you can't immediately see is FALSE. Write down what you found, rebuild one of them as a lookup, and note what got easier the moment the number lived in one place instead of many. Then write the one sentence you want to remember for good. Make it say why XLOOKUP's exact-match default is a safety property and not a convenience. Add why you'll never again type a bare VLOOKUP without asking what its fourth argument is doing.

This module's flashcards and mastery quiz are wired into the app: see the node's Quiz and Reviews.

This module's flashcards and mastery quiz are wired into the app: see the node's Quiz and Reviews.