Check Sheet Logo
Check Sheet

TOCOL

The TOCOL formula returns an array with the column number of each cell in a specified range. This function is useful when you need to know the column number of each cell in a range.

Usage

Use the TOCOL formula with the syntax shown below, it has 1 required parameter and 2 optional parameters:

=TOCOL(array_or_range, [ignore], [scan_by_column])
Parameters:
  1. array_or_range (required):
    The array or range of cells for which to return the column numbers.
  2. ignore (optional):
    Optional. A value or range of values to ignore in the input range.
  3. scan_by_column (optional):
    Optional. If TRUE, the function scans the range by column instead of by row.

Examples

Here are a few example use cases that explain how to use the TOCOL formula in Google Sheets.

Finding the column number of a specific cell

If you need to know the column number of a specific cell, you can use the TOCOL formula to return the column number of a single cell.

Creating a dynamic column reference

The TOCOL formula can be used to create a dynamic column reference in a formula. This is useful when you need to perform calculations on a specific column in a table, but the location of the column changes frequently.

Common Mistakes

TOCOL not working? Here are some common mistakes people make when using the TOCOL Google Sheets Formula:

Forgetting to lock the cell reference

If you forget to lock the cell reference when using TOCOL in a formula, the formula will not work as expected when copied to other cells. Make sure to use $ to lock the cell reference if you want to copy the formula to other cells.

Not providing a range or cell reference

If you don't provide a range or cell reference to TOCOL, the formula will return an error. Make sure to provide a valid range or cell reference as the first parameter.

Related Formulas

The following functions are similar to TOCOL or are often used with it in a formula:

  • ROW

    The ROW formula returns the row number of a specified cell reference. If no cell reference is provided, it returns the row number of the cell in which the formula is entered. This formula is most commonly used for data analysis and manipulation.

  • COLUMN

    The COLUMN formula returns the column number of a given cell reference. It is most commonly used as a helper function for other formulas that require the column number as an argument.

  • ADDRESS

    The ADDRESS formula in Google Sheets returns a cell reference as a string, given the row and column numbers. It can also return an absolute or relative reference, and can include sheet name in the reference. This formula is most commonly used to create dynamic references in other formulas or to create a cell reference based on certain criteria.

  • INDEX

    The INDEX formula in Google Sheets is used to return a value or reference of a cell at the intersection of a specified row and column in a given range. The formula is commonly used to lookup and retrieve values from a table or range of cells.

  • MATCH

    The MATCH formula is used to search for a specified value within a range and returns the relative position of the value within the range. This formula is commonly used in conjunction with the INDEX formula to retrieve a value from a specific cell in a range. The search_type parameter is optional and determines the type of match to be performed, such as exact match, less than, or greater than.

Learn More

You can learn more about the TOCOL Google Sheets function on Google Support.