Lookup & Reference
Increment a Cell Reference Every Nth Row When Copying Down (Non-Volatile INDEX)
Extract every 3rd (or k-th) row from repeating stacked export blocks—such as stacked Vendor / Invoice / Amount triplets—using non-volatile INDEX + ROW math instead of OFFSET or INDIRECT.
Why dragging a formula shifts by 1 row—and how 1 + (ROW(A1) - 1) * k steps by k
When an ERP report exports repeating 3-row blocks in a single column (Row 2 = Vendor, Row 3 = Invoice#, Row 4 = Amount, Row 5 = next Vendor...), dragging =B2 down one row produces =B3 instead of jumping 3 rows to =B5.
Using non-volatile INDEX($B$2:$B$10, 1 + (ROW(A1) - 1) * 3 + offset) maps output row 1 -> index 1, output row 2 -> index 4, and output row 3 -> index 7, reshaping stacked 3-line blocks into a clean 3-column table without volatile OFFSET.
| Summary Row i = ROW(A1) | Vendor Index: 1 + (i-1)*3 | Invoice Index: 2 + (i-1)*3 | Amount Index: 3 + (i-1)*3 |
|---|---|---|---|
| i = 1 (first record) | Index 1 (B2) | Index 2 (B3) | Index 3 (B4) |
| i = 2 (second record) | Index 4 (B5) | Index 5 (B6) | Index 6 (B7) |
| i = 3 (third record) | Index 7 (B8) | Index 8 (B9) | Index 9 (B10) |
Nth-Row Stepping INDEX Formula
Step through $B$2:$B$10 in increments of 3 using ROW(A1) for the 1-based output record counter.
=INDEX($B$2:$B$10, 1 + (ROW(A1) - 1) * 3)Try the example
Use the original values or change them to test your understanding.