INDEX/MATCH, Two-Way Matrix Lookups & Tiered Rate Brackets

Who this is for

Stage 3: Exact, Matrix & Bracket Lookups — Designed for learners mastering 1d & two-way index + match + xmatch, dynamic p&l columns & chooserows/choosecols slices, floor (-1) & ceiling (+1) tiered brackets, compound keys & progressive marginal slabs through interactive spreadsheet examples.

What you will practise

• Extract the warehouse name at row offset G2 from A2:A5 into G4 and the corresponding quarterly throughput from B2:D5 at (G2, G3) into G5.
• Find the 1-based row position of depot H2 inside A2:A5 in H4, and the 1-based column position of month H3 inside B1:E1 in H5.
• Look up Asset Tags ($A$2:$A$6) for scanned Serial Numbers in E2:E5 against $C$2:$C$6 using IFERROR + INDEX + MATCH (fallback "Unknown Serial") in F2:F5.
• Price each shipment in I2:I4 by intersecting Weight Band (G2:G4) and Service Zone (H2:H4) inside $B$2:$E$5, then sum total freight in I5.
• Find the floor VM tier position (XMATCH match_mode = -1) in E3, the ceiling VM tier position (XMATCH match_mode = 1) in E4, and retrieve the ceiling VM hourly cost from B2:B5 into E5.
• Populate G2:G5 with the P&L actuals for month $G$1 from $B$2:$E$5, and compute rounded EBITDA margin ROUND(G5 / G2, 4) in G6.
• Look up each order's volume discount rate in F2:F5 using approximate VLOOKUP(..., 2, TRUE) against $A$2:$B$5, and compute Net Payable E2 * (1 - F2) in G2:G5.
• Look up commission rates in E2:E5 against descending brackets $A$2:$B$5 using XLOOKUP(..., 0, -1), and compute total payout in E6 with SUMPRODUCT(D2:D5, E2:E5).

Your learning path

1. 5x5 Risk Score Buckets & Step-Based Tier Lookup (Sorted INDEX & MATCH)

   Compute Likelihood x Impact risk scores (1–25) and map them into editable Low / Moderate / High / Critical governance tiers using sorted step-threshold lookup.

2. Reverse Left Lookup: Retrieve Values to the Left of the Lookup Column

   Look up an ID or SKU positioned to the left of the search column without rearranging worksheet columns using XLOOKUP or INDEX and MATCH.

3. Case-sensitive lookup with EXACT, XLOOKUP and INDEX/MATCH

   Use a per-row EXACT helper to distinguish letter case in text identifiers, with explicit blank and missing-match handling.

4. Dynamic two-way sum and average: aggregate a chosen column by row criteria

   Select a monthly metric column with XLOOKUP, filter its rows, and calculate a dynamic sum or average.

5. 2D Matrix Dynamic Row Sum, Column Sum & Two-Way Lookup (SUMPRODUCT)

   Sum an entire dynamically matched row, sum an entire dynamically matched column, or retrieve a two-way matrix intersection across a 2D table using boolean-broadcast SUMPRODUCT.

6. Partial-Match Wildcard Lookup: VLOOKUP("*" & Key & "*") & XLOOKUP Mode 2

   Look up vendor records or catalog SKUs by partial embedded code using VLOOKUP("*" & E2 & "*", A2:C5, 3, FALSE) or XLOOKUP("*" & E2 & "*", ..., 2).

7. INDEX and MATCH with multiple criteria: boolean array lookup without XLOOKUP

   Match two or more columns simultaneously with INDEX(return_range, MATCH(1, (col1=x)*(col2=y), 0)) for universal workbook compatibility.

8. Rank & Score Leaderboard with Deterministic Tie-Breaking (RANK + INDEX/MATCH)

   Build a live 1-through-N ranked leaderboard that resolves tied scores deterministically via RANK + COUNTIF running offsets so INDEX and MATCH never repeat the same contender.