Lookup & Reference
Unwrap a stacked column into a 2D table of records (INDEX + ROW/COLUMN)
Reshape copy-pasted PDF or web directories where Name, Role, and Salary repeat every K rows in Column A into clean structured rows and columns.
Turn repeating K-row blocks into clean database records
Pasting contact cards from a PDF or webpage often dumps fields vertically in a single column: A2 = Name1, A3 = Role1, A4 = Salary1, A5 = Name2, A6 = Role2, A7 = Salary2 (repeating every K = 3 rows).
For Record r (1, 2, ...) and Field c (1..K), the linear offset inside $A$2:$A$7 is (r - 1) * K + c. Using (ROW(A1) - 1) * 3 + COLUMN(A1) inside INDEX($A$2:$A$7, ...) lets you drag one formula across 3 columns and down as many rows as needed!
| Record r | Col 1: (r-1)*3 + 1 | Col 2: (r-1)*3 + 2 | Col 3: (r-1)*3 + 3 |
|---|---|---|---|
| r = 1 | Index 1 -> "Alice" | Index 2 -> "Admin" | Index 3 -> 95000 |
| r = 2 | Index 4 -> "Bob" | Index 5 -> "Sales" | Index 6 -> 82000 |
Universal 2D record-unwrapping formula
Wrap INDEX in IFERROR(..., "") so dragging past the last record outputs clean blanks instead of #REF!.
=IFERROR(INDEX($A$2:$A$7, (ROW(A1) - 1) * 3 + COLUMN(A1)), "")Pull Record 2, Field 2 directly
At Row 2, Column 2 (B2 relative to A1), (2 - 1)*3 + 2 = 5 -> "Sales".
=IFERROR(INDEX($A$2:$A$7, (ROW(B2) - 1) * 3 + COLUMN(B2)), "")Inputs and interpretation
Enter the output formula outside A2:A7, then fill exactly three columns. ROW(A1)/COLUMN(A1) act as relative counters, not data values. Preserve empty placeholders for missing fields or later records shift position. IFERROR also hides source-cell errors; investigate them separately. A partial final record needs an explicit completion policy.
Try the example
Use the original values or change them to test your understanding.