Check Sheet Logo
Check Sheet

INDIRECT

The INDIRECT formula is used to return a cell reference specified by a string. This can be useful when you need to dynamically reference a cell based on the value in another cell. The INDIRECT formula can also be used to reference cells in other sheets or workbooks.

Usage

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

=INDIRECT(cell_reference_as_string, [is_A1_notation])
Parameters:
  1. cell_reference_as_string (required):
    The cell reference as a string, which can be in A1 or R1C1 notation. If the cell reference refers to a cell in another sheet or workbook, the sheet or workbook name must be included in the string.
  2. is_A1_notation (optional):
    A boolean value that specifies whether the cell reference is in A1 notation (TRUE) or R1C1 notation (FALSE). If omitted, the formula assumes A1 notation.

Examples

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

Dynamic referencing

You can use INDIRECT to dynamically reference a cell based on the value in another cell. For example, if you have a dropdown list in cell A1 and want to display the value in the corresponding cell in column B, you can use INDIRECT to reference the cell in column B based on the value in A1.

Referencing cells in other sheets

You can use INDIRECT to reference cells in other sheets. For example, if you have a sheet named 'Sheet2' and want to reference cell A1 in that sheet, you can use INDIRECT with the string 'Sheet2!A1'.

Referencing cells in other workbooks

You can use INDIRECT to reference cells in other workbooks. For example, if you have a workbook named 'Workbook2' and want to reference cell A1 in a sheet named 'Sheet1' in that workbook, you can use INDIRECT with the string '[Workbook2]Sheet1!A1'.

Common Mistakes

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

Incorrect cell reference format

If the cell reference provided is not in the correct format (i.e. A1 notation or R1C1 notation), the formula will return an error. Make sure to use the correct format for the reference.

Missing quotation marks

If the cell reference provided is not enclosed in quotation marks, the formula will return an error. Make sure to enclose the reference in quotation marks.

Invalid sheet name

If the sheet name provided in the cell reference is not valid, the formula will return an error. Make sure to use the correct sheet name.

Using a range instead of a single cell reference

If a range of cells is provided as the cell reference, the formula will return an error. Make sure to use a single cell reference.

Using a cell reference in a different sheet

If the cell reference provided is in a different sheet, the formula will return an error. Use the sheet name followed by an exclamation mark before the cell reference to reference a cell in a different sheet.

Related Formulas

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

  • 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.

  • CELL

    The CELL formula returns information about the formatting, location, or contents of a cell. The type of information returned depends on the value of the info_type argument.

  • OFFSET

    The OFFSET formula in Google Sheets returns a cell or range of cells that is a specified number of rows and columns from a starting cell reference. This formula is most commonly used to create dynamic ranges that can expand or contract as data is added or removed from a sheet.

Learn More

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