Lookup & Reference
Step across every Nth column when dragging a formula down rows (INDEX & ROW)
Pull values from every Nth horizontal column (e.g., B2, D2, F2) into a vertical summary list as you copy a formula down rows using non-volatile INDEX and ROW.
Convert wide multi-column exports (Actual, Budget, Actual, Budget...) into vertical rows!
ERP exports often alternate Actual and Budget across horizontal columns (B2 = Q1 Actual, C2 = Q1 Budget, D2 = Q2 Actual, E2 = Q2 Budget, F2 = Q3 Actual, G2 = Q3 Budget). If you drag =B2 vertically down a summary table, spreadsheet relative references increment ROWS (B3, B4), NOT columns!
Instead of volatile OFFSET or INDIRECT, anchor the horizontal strip $B$2:$G$2 inside INDEX and calculate the 1-based column index from vertical row offset: ColIdx = (ROW(A5) - ROW($A$5)) * Step + StartOffset!
| Vertical Summary Row | Row Offset k = ROW()-ROW($A$5) | Col Index (k * 2 + 1) | Pulled from $B$2:$G$2 |
|---|---|---|---|
| Q1 (Row 5) | k = 0 | 0 * 2 + 1 = Col 1 (B2) | 120 (Q1 Actual) |
| Q2 (Row 6) | k = 1 | 1 * 2 + 1 = Col 3 (D2) | 150 (Q2 Actual) |
| Q3 (Row 7) | k = 2 | 2 * 2 + 1 = Col 5 (F2) | 180 (Q3 Actual) |
| Switch Offset = 2 (Budgets) | k * 2 + 2 | Cols 2, 4, 6 (C2, E2, G2) | 110, 140, 175 (Budgets!) |
Pull every Nth horizontal column into vertical rows
B4 is a positive integer step and C4 is a one-based starting column within B2:G2. The row 6 formula shown below is the second output; begin in B5 with ROW(A5) and copy down. Once the calculated source column exceeds six, the formula returns End of source. Invalid configuration is reported separately.
=IF(COUNT($B$4:$C$4)<>2,"Invalid configuration",IF(OR($B$4<1,$C$4<1,$C$4>6,$B$4<>INT($B$4),$C$4<>INT($C$4)),"Invalid configuration",IF((ROW(A6)-ROW($A$5))*$B$4+$C$4>6,"End of source",INDEX($B$2:$G$2, 1, (ROW(A6) - ROW($A$5)) * $B$4 + $C$4))))Step 1 column per row (1:1 horizontal-to-vertical pull)
For a sequential horizontal-to-vertical pull, set B4=1 and C4=1. A copied formula changes the unanchored ROW(A5) reference while keeping the source range and origin fixed. Returned blank cells may be displayed as zero by INDEX; decide explicitly how missing source data should be represented.
=IF(COUNT($B$4:$C$4)<>2,"Invalid configuration",IF(OR($B$4<1,$C$4<1,$C$4>6,$B$4<>INT($B$4),$C$4<>INT($C$4)),"Invalid configuration",IF((ROW(A5)-ROW($A$5))*$B$4+$C$4>6,"End of source",INDEX($B$2:$G$2, 1, (ROW(A5) - ROW($A$5)) * $B$4 + $C$4))))Try the example
Use the original values or change them to test your understanding.