Replace Volatile OFFSET/INDIRECT with INDEX/XMATCH for Faster Excel Models

Why OFFSET and INDIRECT Hurt Performance

Volatile functions: the silent workbook killers

Volatile functions recalculate whenever anything changes in the workbook, not just when their precedents change.

Key volatile functions include:

• OFFSET
• INDIRECT
• TODAY, NOW, RAND, RANDBETWEEN
• CELL, INFO, AREAS

In large models, this means:

• Unnecessary recalculation: one cell change triggers thousands of formula evaluations.
• Laggy user experience: typing, filtering, or refreshing queries feels slow.
• Hard-to-debug behaviour: results change when you don’t expect them to.

OFFSET and INDIRECT are the usual suspects when a model is both “clever” and painfully slow.

The Core Idea: Dynamic Ranges Without Volatility

You often use OFFSET or INDIRECT for two reasons:

1. Dynamic ranges (e.g. last N rows, variable-length ranges)
2. Dynamic references (e.g. pick a sheet/column by name)

INDEX and XMATCH can handle both, without volatility:

• INDEX returns a reference to a cell or range.
• XMATCH finds positions (like MATCH, but more powerful and easier to read).

Together, they give you:

• Dynamic ranges that don’t recalc unnecessarily.
• Clear, auditable formulas instead of string-based references.

Replacing OFFSET with INDEX

Pattern 1: Dynamic last row range

Typical OFFSET pattern (sum last 12 months):

=SUM(OFFSET(B2,COUNTA(B:B)-12,0,12,1))

Issues:

• Volatile because of OFFSET.
• Depends on COUNTA(B:B) over a whole column.

INDEX-based alternative:

Assume dates or values in B2:B1000, no blanks in the middle.

=LET(
    rng, B2:B1000,
    lastRow, MATCH(9.99999999999999E+307,rng),
    SUM(INDEX(rng,lastRow-11):INDEX(rng,lastRow))
)

Explanation:

• MATCH(9.9999E+307,rng) finds the last numeric value.
• INDEX(rng,lastRow-11) and INDEX(rng,lastRow) return start and end cells.
• The colon : between them builds a standard range – non-volatile.

If you don’t have LET, you can inline the logic:

=SUM(INDEX(B2:B1000,MATCH(9.99999999999999E+307,B2:B1000)-11):
     INDEX(B2:B1000,MATCH(9.99999999999999E+307,B2:B1000)))

Pattern 2: Dynamic range with variable height

OFFSET version (sum from B2 down N rows):

=SUM(OFFSET(B2,0,0,$E$1,1))

Where E1 holds the number of rows.

INDEX version:

=SUM(B2:INDEX(B:B,ROW(B2)-1+$E$1))

Explanation:

• ROW(B2)-1+$E$1 gives the ending row number.
• INDEX(B:B, … ) returns the bottom cell.
• The range B2:that_cell is entirely non-volatile.

Pattern 3: 2D OFFSET replacement

OFFSET version (get a cell r rows down and c columns right from B2):

=OFFSET($B$2,$E$1,$F$1)

INDEX version:

=INDEX($B$2:$Z$100, $E$1+1, $F$1+1)

Why +1?

• If E1=0, we want the first row of the block starting at B2, which is row 1 within B2:Z100.
• Same logic for columns.

This is faster and explicit: you see the whole area (B2:Z100) instead of an opaque OFFSET.

Replacing INDIRECT with INDEX/XMATCH

INDIRECT turns text into a reference. That’s powerful, but:

• It’s volatile.
• It breaks when sheet names change.
• It’s hard to trace, because precedents are hidden inside strings.

Pattern 4: Dynamic column by header name

INDIRECT version:

=SUM(INDIRECT("Sales!" & $B$1 & "2:" & $B$1 & "100"))

Where B1 contains a column letter like "D".

INDEX/XMATCH version – no column letters, just headers:

=LET(
    hdrs, Sales!$A$1:$Z$1,
    data, Sales!$A$2:$Z$100,
    colPos, XMATCH($B$1, hdrs),
    SUM(INDEX(data,,colPos))
)

Benefits:

• Uses header names instead of fragile column letters.
• XMATCH finds the correct column position.
• INDEX(data,,colPos) returns the whole column as a range.

Without LET:

=SUM(INDEX(Sales!$A$2:$Z$100,, XMATCH($B$1, Sales!$A$1:$Z$1)))

Pattern 5: Dynamic sheet selection (safer design)

This is the one use case where INDIRECT seems unavoidable:

=SUM(INDIRECT($A$1 & "!B2:B100"))

Where A1 contains the sheet name.

There is no non-volatile formula that directly swaps sheet names. Instead, consider these design alternatives:

1. Consolidate data into a single table with a "Sheet" or "Scenario" column, then filter by that column.
2. Use structured references and FILTER/SUMIFS instead of jumping across sheets.

Example with consolidated table tblData:

• Columns: Scenario, Month, Amount
• Cell A1 holds the selected scenario.

=SUMIFS(tblData[Amount], tblData[Scenario], $A$1)

This replaces the need to point to different sheets and is more robust for reporting and Power BI.

Pattern 6: Dynamic named ranges without INDIRECT

INDIRECT version:

=SUM(INDIRECT($B$1))

Where B1 contains the name of a named range.

Better pattern: avoid storing names as text; store the range directly.

Example using CHOOSE to map options to ranges:

• B1 contains an index: 1 = Sales, 2 = Costs, 3 = Margin.

=LET(
    choice, $B$1,
    rng, CHOOSE(choice, SalesRange, CostsRange, MarginRange),
    SUM(rng)
)

You’ve removed INDIRECT entirely, and Excel can now trace the precedent ranges properly.

INDEX + XMATCH Patterns You’ll Reuse Everywhere

1. Two-way lookup (row + column)

Instead of INDEX(MATCH(),MATCH()) with manual ranges, use named ranges and XMATCH.

Assume:

• hdrs = header row
• keys = key column
• data = full data block

=LET(
    r, XMATCH($A$2, keys),
    c, XMATCH($B$2, hdrs),
    INDEX(data, r, c)
)

This replaces many INDIRECT-based constructions used to simulate dynamic tables.

2. Dynamic spill range from last N rows

If you’re on 365, you can spill the last N values without OFFSET:

=LET(
    rng, B2:B1000,
    n, $E$1,
    lastRow, XMATCH(9.99999999999999E+307, rng),
    startRow, MAX(1, lastRow - n + 1),
    INDEX(rng, SEQUENCE(n,1,startRow))
)

• SEQUENCE generates the row offsets.
• INDEX returns a spilled array of the last n values.

Again, no volatile functions involved.

Migration Strategy: How to Clean Up an Existing Model

You don’t have to refactor everything at once. Prioritise the worst offenders.

1. Find volatile formulas

   • Use Formulas → Name Manager to inspect named ranges using OFFSET/INDIRECT.
   • Use Find & Select → Find for OFFSET( and INDIRECT(.

2. Start with named ranges

   • Replace OFFSET-based names with INDEX-based definitions.
   • This alone can dramatically stabilise a model.

3. Refactor repeated patterns

   • Identify formulas used thousands of times (e.g. in whole columns).
   • Replace once, fill down, and test.

4. Test with manual calculation

   • Switch to Formulas → Calculation Options → Manual.
   • Press F9 and watch calculation time before/after refactors.
   • You don’t need exact timings; you’ll feel the difference in large files.

5. Document the patterns

   • Add a hidden sheet or a README tab with examples of your INDEX/XMATCH patterns.
   • Future you (and your team) will reuse them instead of going back to OFFSET/INDIRECT.

Practical Takeaway

Whenever you reach for OFFSET or INDIRECT, pause and ask:

Can I express this as a normal range built from INDEX + XMATCH (and maybe SEQUENCE/CHOOSE)?

If the answer is yes, use the non-volatile pattern. Over time, your models will:

• Calculate faster.
• Be easier to debug.
• Survive structural changes (new columns, new rows, renamed sheets) with far fewer surprises.

Start by refactoring just one high-impact OFFSET or INDIRECT block in your busiest workbook today. The performance gain will usually be enough to justify doing the rest.