Syntax:
TOCOL(Range [; Ignore] [; ByColumn])
Description:
This function returns a cell range in a list as a single column. You can optionally choose whether the search direction for returning the data should be by row or by column and whether you want to ignore empty entries or errors.
Note: With the TOCOL function, the data is returned in a vertical direction. To return in a horizontal direction, use TOROW instead.
Range is the cell range or array from which the values are to be returned in a list.
Ignore (optional) specifies whether certain types of values should be ignored. If omitted, the default value 0 is used (none of the values are ignored).
0 = All values will be kept
1 = Cells with empty values will be ignored
2 = Cells with error values will be ignored
3 = Cells with empty values and error values will be ignored
ByColumn (optional) specifies whether the returned data should be sorted by row or by column. FALSE searches the range in the direction of the row and TRUE in the direction of the column. If omitted, FALSE is used as the default value and thus row 1 is searched first horizontally, then row 2 horizontally, etc.
Note:
The function is designed so that it can automatically "spill" over into adjacent cells as required. In addition, the result is automatically updated when changes are made to the initial list. For this reason, such functions are also called dynamic matrix formulas.
If the function causes a spill over to adjacent cells in the results area, a #SPILL! error value will be displayed if those cells are already filled with content.
Compatibility notes:
Microsoft Excel supports this function only in version 2024 or later. The function is unknown in older versions.
Examples:
Example 1: Search list row by row
The formula TOCOL is applied in cell E2:
TOCOL(A1:C3) returns in column E a list of all data from the Range with row-by-row search direction.
Example 2: Search list row by row and ignore empty cells
The formula TOCOL is applied in cell E2:
TOCOL(A1:C3, 1) returns in column E a list of all data from the Range with row-by-row search direction. Empty cells were removed.
Example 3: Search list column by column
The formula TOCOL is applied in cell E2:
TOCOL(A1:C3, , TRUE) returns in column 5 a list of all data from the Range with column-by-column search direction.
See also: