Google Sheets INDIRECT Function: Syntax, Examples & Tips

cell_reference_as_string:
A string that represents the cell reference or range of cells you want to refer to. This can be a single cell (e.g., "A1") or a range (e.g., "A1:B2").

is_A1_notation:
An optional boolean value that specifies whether the cell_reference_as_string is in A1 notation (TRUE) or R1C1 notation (FALSE). If omitted, it defaults to TRUE.

What is an INDIRECT Function?

The INDIRECT function in Google Sheets is a powerful tool that allows you to reference a cell or range of cells indirectly. This means that instead of directly specifying the cell reference, you can use a text string to create a reference. This can be particularly useful when you want to create dynamic references that change based on other cell values.

In simpler terms, INDIRECT allows you to build references to cells or ranges in a flexible way, making your formulas more adaptable to changes in your data.

A Practical Example

Imagine you are managing a sales report where you have monthly sales data stored in different sheets:

January Sales Table:

February Sales Table:

You want to create a summary table that dynamically pulls the sales data for each month based on the month selected in another cell.

INDIRECT Formula

To achieve this, you would use the INDIRECT function as follows:

=INDIRECT(A1 & "!C2")

In this formula:

• A1 is the cell containing the name of the month (e.g., "January" or "February").
• "!C2" specifies the cell reference you want to pull data from (in this case, the Sales for ProductID 101).
• The combination of A1 & "!C2" creates a dynamic reference to the correct cell based on the month selected.

Result of the Formula

When you apply the INDIRECT formula to your summary table, the output would look like this:

If you change the value in cell A1 to "February", the INDIRECT function will automatically update the sales figure for ProductID 101 to reflect the February sales.

Why Use INDIRECT?

INDIRECT is beneficial because it allows for greater flexibility in your formulas. It enables you to create references that can change based on user input or other cell values, making your spreadsheets more dynamic. Additionally, it can help simplify complex formulas by breaking them down into manageable parts.

Key Takeaways:

• INDIRECT: A versatile function that allows you to reference cells or ranges indirectly using text strings.
• Dynamic References: Enables the creation of references that can change based on other cell values, enhancing flexibility.
• Simplifies Formulas: Helps to manage complex formulas by allowing you to break down references into simpler components.
• Common Use Cases: Ideal for summary reports, dynamic dashboards, and any scenario where data needs to be pulled from different sheets or ranges based on user input.

INDIRECT is an essential function for anyone working with data in Google Sheets, providing a powerful way to enhance data analysis and reporting capabilities.

Happy spreadsheeting!