What does INDEX(SEQUENCE()) do in Google Sheets?

`INDEX(SEQUENCE())` generates a dynamic array of row or column positions using SEQUENCE, then uses INDEX to return the values at those positions from a given range. The result is a contiguous slice of data that updates when SEQUENCE's inputs change. Unlike OFFSET, INDEX is non-volatile, so it doesn't recalculate every time any cell in the workbook changes - only when its own inputs do.

Why replace OFFSET with INDEX(SEQUENCE())?

OFFSET is a volatile function in Google Sheets, meaning it recalculates on every worksheet change regardless of whether its own inputs changed. According to Google's documentation on volatile functions, INDIRECT and RAND behave the same way. On a financial model with 200+ OFFSET calls across 8 linked tabs, this causes 3-5 second recalculation delays on a typical machine. INDEX(SEQUENCE()) produces the same dynamic range but only recalculates when the specific inputs to SEQUENCE change, cutting recalculation overhead by 40-60% in typical three-statement models.

Can INDEX(SEQUENCE()) replace INDIRECT for cross-tab references?

Partially. INDIRECT is volatile and constructs range references from text strings - useful when the tab name itself is dynamic. INDEX(SEQUENCE()) can't replace text-constructed references to variable tab names. But when you know which tab you're pulling from and just need a dynamic row offset, `=INDEX('P&L'!C:C, SEQUENCE(n, 1, start, 1))` is a clean non-volatile alternative to `=INDIRECT("'P&L'!C" & start & ":C" & (start+n-1))`.

Does SEQUENCE work inside ARRAYFORMULA?

Yes. `=ARRAYFORMULA(INDEX(range, SEQUENCE(n, 1, start, 1)))` is valid and spills n rows of results. SEQUENCE already returns an array, so ARRAYFORMULA is only needed when combining it with functions that don't natively handle arrays. When used directly with INDEX, the combination spills automatically in Google Sheets without wrapping in ARRAYFORMULA.

What's the difference between INDEX(SEQUENCE()) and FILTER in Google Sheets?

FILTER returns rows that meet a condition. INDEX(SEQUENCE()) returns a positional slice - consecutive rows starting at a specific offset. Use FILTER when you need `Region = "APAC"` or `Revenue > $1M`. Use INDEX(SEQUENCE()) when you need "the 12 rows starting at period X." For rolling time-series windows in financial models, INDEX(SEQUENCE()) is almost always the right choice because the data is already sorted by period and you need a contiguous block, not a filtered subset.

INDEX(SEQUENCE()) in Google Sheets: The Complete Guide

Why OFFSET Is a Problem at Scale

Google's official Sheets documentation classifies OFFSET as a volatile function - meaning it recalculates on every change, not just when its direct inputs change. INDIRECT falls in the same bucket. On a workbook with 8+ linked tabs rolling up quarterly data across P&L, Balance Sheet, and Cash Flow, that volatility compounds. You're not recalculating one OFFSET. You're recalculating every OFFSET in the chain, simultaneously.

The practical result: a 10MB three-statement model with ~200 OFFSET calls can show 3-5 second recalculation delays on a fast machine. Moving to INDEX(SEQUENCE()) cuts that by 40-60% in most cases.

How INDEX and SEQUENCE Work Together in Google Sheets

SEQUENCE generates an array of integers. INDEX uses those integers as row or column positions to pull values from a range. Together they produce a dynamic slice of data that behaves like OFFSET but without the volatility.

The core pattern:

=INDEX(range, SEQUENCE(n, 1, start_row, 1))

Where n is how many rows to return and start_row is the offset from the top of the range. For a rolling 12-month P&L slice where your Assumptions tab holds the start period:

=INDEX('P&L'!C:C, SEQUENCE(12, 1, MATCH(Assumptions!$B$3, 'P&L'!$B:$B, 0), 1))

This returns 12 consecutive values from column C of the P&L tab, starting at the row matching whatever period is in Assumptions!$B$3. Change the assumption and the slice moves. No OFFSET, no volatility.

SEQUENCE was introduced to Google Sheets in 2019 as part of a broader expansion of array-native functions. According to the official SEQUENCE function reference, it takes up to 4 arguments: rows, columns, start, and step. As of July 2026, it's available in all Google Workspace tiers with no row limit beyond the spreadsheet's overall 10 million cell cap.

Pattern 1: INDEX(SEQUENCE()) for Dynamic Period Slices

The most common FP&A use case: a rolling window across months. Your P&L has revenue in column C, month labels in column B (Jan-2026, Feb-2026, etc.). You want the last 12 months flowing into a summary without hard-coded row references.

// Returns 12 consecutive revenue rows starting from the period in Assumptions!B3
=ARRAYFORMULA(
  INDEX('P&L'!C:C,
    SEQUENCE(12, 1, MATCH(Assumptions!$B$3, 'P&L'!$B:$B, 0), 1)
  )
)

Wrap it in ARRAYFORMULA and it spills down 12 rows automatically. The MATCH pins the start row to whatever period your Assumptions tab specifies. This is the building block for every rolling window in a multi-tab model.

Pattern 2: INDEX(SEQUENCE()) for a 2D Block Across Tabs

When both rows and columns need to be dynamic - say, 4 quarters × 3 line items from an Actuals tab into a returns analysis - SEQUENCE handles both dimensions:

// Pull a 4-row × 3-column block starting at the current period
=INDEX(
  'Actuals'!$C$2:$Z$100,
  SEQUENCE(4, 1, MATCH(Assumptions!$B$3, 'Actuals'!$B$2:$B$100, 0), 1),
  SEQUENCE(1, 3, 1, 1)
)

The row SEQUENCE walks down 4 periods. The column SEQUENCE walks across 3 line items. Google Sheets returns the intersection as a 4×3 array. Pipe that into SUMPRODUCT and you have a fully dynamic cross-tab reference that recalculates only when Assumptions!B3 changes.

Pattern 3: Building a Dynamic Date Spine

LBO and DCF models need a date spine that adjusts when the projection start date changes. SEQUENCE generates it directly:

// Generate 60 monthly period-end dates starting from Assumptions!B2
=ARRAYFORMULA(
  EOMONTH(Assumptions!$B$2, SEQUENCE(60, 1, 0, 1) - 1)
)

60 rows, starting at 0 offset from B2, incrementing by 1 month each row. Change B2 and the entire date column updates. Feed that column into the MATCH from Pattern 1 and the whole model re-anchors without touching a single hard-coded date.

Pattern 4: INDEX(SEQUENCE()) for Joining Actuals and Projections

Consider a board pack with a 14.2x EBITDA exit multiple driving a returns summary. The waterfall needs 36 months of actuals from an Actuals tab and 24 months of projections from a Projections tab, joined at the current period.

// Actuals: the 36 months ending at Assumptions!B3
=ARRAYFORMULA(
  INDEX('Actuals'!$D:$D,
    SEQUENCE(36, 1, MATCH(Assumptions!$B$3, 'Actuals'!$B:$B, 0) - 35, 1)
  )
)

// Projections: 24 months starting one period after B3
=ARRAYFORMULA(
  INDEX('Projections'!$D:$D,
    SEQUENCE(24, 1, MATCH(Assumptions!$B$3, 'Projections'!$B:$B, 0) + 1, 1)
  )
)

Both arrays are non-volatile. The model recalculates only when B3 changes. On a 10MB workbook this is the difference between a model that feels responsive and one where every cell edit triggers a 4-second freeze.

Where INDEX(SEQUENCE()) Wins vs. OFFSET, INDIRECT, and FILTER

Here's what the function comparison looks like head-to-head:

Function	Volatile?	Dynamic Range?	2D Support?	Works in ARRAYFORMULA?
OFFSET	Yes	Yes	Yes	No
INDIRECT	Yes	Yes (via text)	Partial	No
INDEX(SEQUENCE())	No	Yes	Yes	Yes
FILTER	No	Conditional only	No	Yes

The volatile column is the one that matters for large models. Google's official documentation on volatile functions states they "are recalculated whenever any value in the spreadsheet changes," regardless of whether their inputs changed. INDEX is explicitly non-volatile.

FILTER and INDEX(SEQUENCE()) aren't competing tools - they solve different problems. FILTER returns rows that meet a condition. INDEX(SEQUENCE()) returns a positional slice of consecutive rows. The practical split:

Need	Use
Rows where Region = "North"	FILTER
Last 12 rows of a time series	INDEX(SEQUENCE())
Multiple condition matches	SUMIFS or FILTER
Rolling window from period X to X+n	INDEX(SEQUENCE())
2D block starting at a known position	INDEX(SEQUENCE())
Non-contiguous multi-row lookup	FILTER

Google Sheets supports up to 10 million cells per spreadsheet, so FILTER on a 100,000-row actuals export works fine for conditionals. But when you're chaining 8 tabs of period-aligned data, positional indexing with SEQUENCE is cleaner and faster than a FILTER evaluating every row.

Rewriting a Model You Inherited

If you've inherited a model where OFFSET is load-bearing in 40 places, rewriting by hand is tedious and error-prone. The offset calculation has to stay correct - flip a sign and your period alignment breaks in a way that won't surface until someone spots a number that doesn't tie to the prior month.

ModelMonkey can scan your existing tabs for OFFSET and INDIRECT calls and rewrite them to INDEX(SEQUENCE()) equivalents with the correct offsets preserved. Useful when you don't want to audit every instance manually.

Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.