Lookups and what-ifs for models

A model is a sheet where you change an input and watch the answer move. Three techniques do most of the work.

Two-way lookups. A table with labels down the side and across the top, like targets by region and quarter, needs two matches: one for the row, one for the column.

=INDEX(B2:E6, MATCH(H1, A2:A6, 0), MATCH(H2, B1:E1, 0))
=XLOOKUP(H1, A2:A6, XLOOKUP(H2, B1:E1, B2:E6))

The inner XLOOKUP returns the whole Q3 column; the outer one picks the East row from it. Both versions update when someone changes H1 or H2.

Two conditions, one row. When neither column alone identifies the row (a rep appears every month, a month has every rep), match on both:

=INDEX(C2:C13, MATCH(1, (A2:A13=F1)*(B2:B13=F2), 0))

Each comparison is a column of TRUE and FALSE. Multiplied, they're 1 only where both hold, and MATCH finds the first 1. Add conditions by multiplying more comparisons. FILTER(C2:C13, A2:A13=F1, B2:B13=F2) gets the same answer and returns every match, which is safer when duplicates are possible.

Scenarios and sensitivity. Put every assumption on an Inputs tab, one column per scenario, and a single cell that names the live one:

Make B1 a dropdown (Data › Data validation, list: Base, Upside, Downside) and point every formula at the Live column. One click re-runs the whole model.

A sensitivity table answers "what if two inputs both move?" Excel has a Data Table tool for this; Sheets doesn't, and doesn't need it. A column of prices times a row of volumes spreads into a grid of every pair:

=ARRAYFORMULA((A3:A7 - H1) * B2:F2 - H2)

Prices run down A3:A7, volumes across B2:F2, unit cost and fixed costs sit in H1 and H2. One formula fills all 25 cells, and it recalculates when costs change. Two conventions keep models honest: type inputs in blue and formulas in black, and never type a number inside a formula.