◆ Data & Lookups · drill · Spreadsheet Formulas & Data track
Two-way lookup
two MATCHes read the labels on both axes — the intersection holds when the segments re-sort and the quarter columns roll.
The reporting tape carries five segments against the last five quarters — the segments re-sort by size every refresh and the quarter window rolls forward, so nothing sits where it sat last week, and this morning the newest column came back without its header. Three pages want one number each off that tape: the flash card beside it, and two board-pack lines below it, one of which already reads the wrong intersection. Read the tape by its labels, repair the pack line, and border the pack before it goes to print.
The shortcuts in this drill
Alt walks: tap Alt, release, then the letters in sequence — each key steps one ribbon menu. On a Mac, tap ⌥ and use the same letters (KeyTips, Excel 2024+).
Ctrl+C⌘C on macCopy
Copy starts almost every hand-off — grab a finished block once, then place it in the summary, the deck, or next quarter’s column.
Ctrl+V⌘V on macPaste
Plain paste brings everything: values, formulas, formats. Reach for Paste Special when you only want part of it.
Alt h b p⌥ h b p on macTop border
Top border above a total row — the accounting ruling that says “this line sums the ones above.”
functions you'll type: INDEX() · MATCH()
The optimal line
the par-setting sequence, straight through:
F4 Q3 FY25 · I6 =INDEX($B$5:$F$9,MATCH(I4,$A$5:$A$9,0),MATCH(I5,$B$4:$F$4,0)) · copy I6 → paste B14 and E14 · A12:E12 alt h b p · ctrl+s
More Data & Lookups drills