INDIRECTformula 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
INDIRECTformula can also be used to reference cells in other sheets or workbooks.
- How to use
- Examples of using
INDIRECTformula not working?
- Similar formulas to
INDIRECT formula with the syntax shown below, it has 1 required parameter and 1 optional parameter:
- 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.
- 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.
ExamplesHere are a few example use cases that explain how to use the
INDIRECTformula in Google Sheets.
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'.
INDIRECTnot working? Here are some common mistakes people make when using the
INDIRECTGoogle 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.
The following functions are similar to
INDIRECT or are often used with it in a formula:
ADDRESSformula 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.
CELLformula returns information about the formatting, location, or contents of a cell. The type of information returned depends on the value of the
OFFSETformula 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.
You can learn more about the
INDIRECT Google Sheets function on Google Support.