Why does my cross-row formula return the wrong value after I sort a FILTER range?

FILTER returns a virtual array where each output row's physical position on the sheet is unrelated to its position in the source data. After sorting, row 5 of your spill might contain what was row 23 of the source. Any formula using OFFSET, direct relative row references (like `=G5-G4` expecting adjacent periods), or ROW()-based indexing will point at the wrong source record because it's navigating by physical sheet position, not by a data key that travels with the record.

Can I use OFFSET safely inside a FILTER output?

Only if the filter criterion and sort order are fixed and the output size never changes - which is rare in a live model. The moment FILTER drops or adds a qualifying row, OFFSET's row count shifts and you pull data from the wrong record. The safe replacement is XLOOKUP or INDEX/MATCH keyed on a stable identifier (period, deal ID, entity code) rather than a row offset.

My month-over-month delta formula worked fine before I added SORT. What changed?

Adding SORT reshuffles which records land in which physical rows of the spill. A formula like `=G5-G4` that previously compared January to February now compares whatever two records happen to sort into positions 4 and 5 - often the two highest or lowest values, not adjacent periods. Move the period-over-period calculation to a helper column in your source tab, include that column in the FILTER range, and let the delta travel with the data rather than being computed in the view layer.

How do I pull "the previous period's value" from a sorted FILTER output?

Don't navigate to the previous row in the filtered output - navigate back to the source by key. XLOOKUP's `search_mode = -1` parameter finds the nearest match searching backward through the source, which effectively retrieves the prior period: ``` =XLOOKUP( 'P&L'!A5, -- current period key 'P&L'!$A:$A, -- all source periods 'P&L'!$C:$C, -- value column 0, 0, -1 -- exact match, search backward ) ``` This finds the record in the source regardless of what row it occupies in any filtered view.

Does this same issue affect QUERY and UNIQUE functions?

Yes. QUERY, UNIQUE, and any dynamic array function that produces variable-length output all share the same behavior: the spill range size can change on recalculation, and any positional formula anchored to it will drift. QUERY is particularly common in financial models for grouping and filtering P&L data, and cross-row reference failures surface identically. As of June 2026, Google's documentation for QUERY doesn't explicitly warn about this interaction with positional formulas, which is why the bug often goes undetected until a number in a board pack fails to reconcile.

Cross-Row Formulas Break After Sort in Filter Range

Why Cross-Row Formulas Break After Sort in a Filter Range

FILTER doesn't reorder source data. It builds a new array in memory where row 1 of the output might be row 23 of your source, row 2 might be row 7, and so on. Add a SORT wrapper:

=SORT(FILTER('P&L'!A:F, 'P&L'!C:C > Assumptions!$B$3), 3, FALSE)

...and you get a fully reshuffled array. The output spilling into, say, Returns Analysis!B5:G16 looks like a table, but each row's physical address on the sheet is just where the spill landed. It has no relationship to the original row number.

Now put a formula in the column next to that spill:

=OFFSET($B$5, ROW()-ROW($B$5), 2)

This walks down the filtered output row by row. It works while the data is static. The moment FILTER or SORT re-evaluates - new date, new filter criterion, one fewer qualifying row - the reference drifts by however many rows shifted, and you're reading from a completely different record.

Google's FILTER documentation states: "The output range of FILTER will be different sizes depending on the source data." That's the key phrase. A spill range whose size can change destroys any positional navigation built around it.

Three Cross-Row Formula Patterns That Fail After Sorting Filtered Data

Every broken formula in this category falls into one of these three shapes.

Pattern 1: OFFSET with a ROW() anchor. =OFFSET($B$5, ROW()-ROW($B$5), 2) works fine in a static table. In a filtered/sorted spill, OFFSET still points at the physical sheet row, not the nth record of the filter output. After a re-sort, ROW()-ROW($B$5) returns the same integer it always did - but the record at that position has changed.

Pattern 2: Relative row references across spill boundaries. Your FILTER output covers B5:G16 (15-row source, 12 qualify). In column H you write =G5-G4, intending month-over-month delta. November sits at $4.2M and April at $3.1M, so the delta should be -$1.1M. But FILTER excludes some months, and after sorting by revenue descending, G4 and G5 are no longer adjacent periods - they're the two highest-revenue months in the entire set. Your delta formula now shows a meaningless spread.

Pattern 3: ROW() used as a positional lookup key. =INDEX(Assumptions!$D:$D, ROW()-4) treats physical row number as an index into a parallel table, assuming row 5 = record 1, row 6 = record 2, and so on. FILTER's variable-length output breaks that mapping immediately, and unlike Pattern 1 and 2, this often produces no error - just silently wrong assumptions pulled from the wrong row.

Fix Cross-Row Formula Errors: Replace Positional References with Key-Based Lookups

The fix for all three patterns is the same: stop navigating by position, start navigating by a stable identifier.

For Patterns 1 and 3: XLOOKUP or INDEX/MATCH on a data key.

If your filtered output includes a period key (month, quarter, deal ID), drive every cross-row calculation off that key rather than off ROW() or OFFSET:

=XLOOKUP(
  'Returns Analysis'!$A5,     -- current row's period key
  'P&L'!$A:$A,                -- source period column
  'P&L'!$C:$C                 -- source value column
)

This survives any sort order because it re-finds the right source row every time, regardless of where that row landed in the spill.

For Pattern 2: Calculate the delta in the source, not in the spill.

Don't compute period-over-period change inside the filtered output. Compute it in a helper column on the source tab, then include that column in the FILTER:

-- Helper column H on P&L tab (period delta):
=G2 - XLOOKUP(A2, 'P&L'!$A:$A, 'P&L'!G:G, 0, 0, -1)

-- Then in Returns Analysis:
=SORT(
  FILTER('P&L'!A:H, 'P&L'!C:C > Assumptions!$B$3),
  3, FALSE
)

The delta is baked into the array before FILTER sees it. Sort order is irrelevant.

Pattern	Broken Formula	Why It Fails	Fixed Approach
OFFSET + ROW()	`=OFFSET($B$5, ROW()-ROW($B$5), 2)`	Points at source row, not output position	`=XLOOKUP(A5, 'P&L'!$A:$A, 'P&L'!$C:$C)`
Adjacent-row delta	`=G5-G4`	Rows aren't adjacent periods after sort	Pre-compute delta in source, FILTER the column
ROW() as index	`=INDEX(Assumptions!$D:$D, ROW()-4)`	Row offset breaks with variable filter size	Key-based XLOOKUP on stable identifier

The Architectural Rule (As of June 2026)

Treat any spill range from FILTER, SORT, UNIQUE, or their combinations as a read-only display layer. The moment you anchor a formula to a position within a spill range, you've coupled your logic to an output that can silently change size and order.

Every calculation that needs to survive across filter or sort changes belongs in the source data layer - a column on your P&L tab, your Assumptions tab, wherever the raw data lives. The filtered/sorted output is just a view.

This matters at scale. A 40-tab model where five tabs pull from the same P&L via FILTER is five places where position-based cross-row formulas can fail simultaneously. And the failure mode is the worst kind: not a #REF! that catches the eye in QC, but a subtly wrong number sitting in the middle of a $38.5M revenue plan.

According to Google's documentation for dynamic array functions, spill ranges are evaluated fresh on each recalculation - there's no guarantee that the nth row of a prior evaluation maps to the nth row of the current one. That behavior is by design, but it's exactly what position-dependent cross-row formulas can't handle.

When You Can't Redesign the Formula

Sometimes the source is read-only (a live import, a shared sheet you don't control) and adding helper columns isn't an option. Two approaches work here.

Option A: Pre-aggregate with SUMIFS before FILTER touches the data.

=SUMIFS(
  'P&L'!C:C,
  'P&L'!B:B, ">=" & Assumptions!$B$3,
  'P&L'!D:D, 'Returns Analysis'!$A5
)

SUMIFS navigates by criteria, not by position, so sort order inside any filtered view doesn't matter.

Option B: Use XLOOKUP with backward search to find the prior period.

=XLOOKUP(
  'P&L'!A5,      -- current period key
  'P&L'!$A:$A,   -- all periods in source
  'P&L'!$C:$C,   -- value column
  0, 0, -1       -- exact match, search backward
)

Neither approach is as clean as fixing the source. But they avoid the positional coupling entirely and work correctly regardless of how many rows FILTER returns on any given recalculation.

ModelMonkey can audit a model for OFFSET and row-relative references sitting near FILTER or SORT formulas. If you've inherited a multi-tab deck from a predecessor and need to find every instance of this pattern before the Q3 board pack goes out, that's faster than manually tracing 40 tabs.

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