FormulaCamp
TOCOL with worked examples you can test in the live grid. The argument details are in the Excel guide.
The arguments and common errors are covered in the TOCOL Excel guide; check the function help in Google Sheets for its own argument list.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Rep | Amount |
| 2 | East | Ana | 1200 |
| 3 | West | Ben | 850 |
| 4 | East | Cara | 430 |
| 5 | North | Dan | 970 |
| 6 | West | Eli | 1500 |
Formula: F2: =TOCOL(B2:C3)
Result: Spills down: Ana, 1200, Ben, 850
The default order reads the first row, Ana and 1200, then the second row.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Rep | Amount |
| 2 | East | Ana | 1200 |
| 3 | West | Ben | 850 |
| 4 | East | Cara | 430 |
| 5 | North | Dan | 970 |
| 6 | West | Eli | 1500 |
Formula: F2: =TOCOL(B2:C3,,TRUE)
Result: Spills down: Ana, Ben, 1200, 850
With scan_by_column TRUE, the names come first, then the amounts.
| A | B | |
|---|---|---|
| 1 | a | b |
| 2 | d |
Formula: D1: =TOCOL(A1:B2,1)
Result: Spills down: a, b, d
The ignore code 1 leaves out the blank cell, so only three items remain.
A tiny spreadsheet with example data. Edit the formula or any number and see the result.
Formula in F2: =TOCOL(C2:D3)
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Units | Price | Result | |
| 2 | East | Pen | 120 | 2.5 | 120 | |
| 3 | West | Pen | 80 | 2.5 | 2.5 | |
| 4 | East | Book | 45 | 12 | 80 | |
| 5 | North | Book | 60 | 12 | 2.5 | |
| 6 | West | Lamp | 30 | 25 | ||
| 7 | East | Lamp | 15 | 25 |
Google Sheets is used here only as a descriptive product name. FormulaCamp is not affiliated with Google.
Enter — commit · F2 — edit · F4 — $ in a reference · Ctrl+C/Ctrl+V — copy and paste · Ctrl+Z — undo
Esc or F6 — leave the sheet for the «Check» button · Shift+Tab in the first cell — back to the page