INDIRECT
TheINDIRECT
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.
 How to use
INDIRECT
formula?  Examples of using
INDIRECT
formula INDIRECT
formula not working? Similar formulas to
INDIRECT
Usage
Use the 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.
Examples
Here are a few example use cases that explain how to use theINDIRECT
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 theinfo_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.