The INDIRECT function in Google Sheets is useful if you want to return a cell reference specified by a string.
The function is often used as a referencing tool in a formula to a cell or range that is readily available.
Table of Contents
Let’s take an example:
By selecting A2, the return value would be “123”, as it is the cell reference in A4.
INDIRECT function only understands cell reference addresses in Google Sheets. Hence, by selecting A3, the return value becomes “#REF!”, meaning the formula is not valid.
This is because the
INDIRECT function could not understand “Hello” as it is just plain text and not a cell reference address that the function can refer to.
INDIRECT function would be really handy in real-world situations!
The Anatomy of the INDIRECT Function
The way we write the
INDIRECT Function is:
Let us help you understand the context of the function:
- The equal sign
=is how we start any function in Google Sheets.
INDIRECT()is our function. We need to add two attributes, namely the
[is_A1_notation], to make it work correctly.
cell_reference_as_stringis a cell reference, written as a string.
[is_A1_notation]is an expression used to indicate what style notation to display the cell reference in. This attribute is optional.
Let’s have a more detailed understanding of what
Is A1 Notation:
This attribute is to determine how the cell reference’s address would be displayed.
Here are the references:
A Real Life Example of Using INDIRECT Function
Let’s use a real-life situation to utilize the
INDIRECTfunction to show how powerful this function can be!
Here is a summary of the total profit for the first quarter of 2020 that is linked to different month’s tab.
This example would show how to use the
INDIRECT function to refer cell references from several other tabs.
How to Use INDIRECT Function in Google Sheets
- Simply click on the cell that you want to write down your function at. In this example, it will be B2.
- Begin your function with an equal sign
=, followed by the name of the function,
SUM, and an open parenthesis
INDIRECT, then another open parenthesis
- We will select A2 as our
By selecting A2, we are referring the formula to the tab named ‘Jan‘.
- Then, add an ampersand
&to join two or more text strings in a single string.
- Next, enclosed by a quote-unquote symbol
"", select the cell we want to refer to.
We have selected “!B4:D13” as this is the range of cells where the profit would be sum-up in all three tabs.
- After the following steps, your input should look like this:
Let us show you how to use the
INDIRECT function in a more complex formula.
By using a mixture of functions and tools in Google Sheet, we have created a template to perform profit comparisons between different branches in different months.
Here is a rundown of the tools and functions involved:
- Data Validation tool – restricts the type of data or the values a user enters into a cell. In our case, it creates a drop-down to select which branch and month we would like to use as a comparison.
VLOOKUPfunction – helps pull out data from a complex database or tables.
For more in-depth explanations and examples for the
VLOOKUP function, click here!
- To create data validation on a cell. First, select the cell you want to validate, which is A3.
- Next, click on ‘Data‘ on the Google Sheet Toolbar.
- Select ‘Data Validation‘.
- Once clicked, this is what would display on your screen:
- Then, select the range of data you would like to validate. Using the same database as Example 1, we have selected all the branch names. Simply select the listed branch name from any tab.
- After selecting the range of data, click Save.
- After clicking Save, the cell would display an arrow that would drop down to a list of branch names.
- Do the same for the “Month” column.
You can also manually list down the items.
- Now let’s move on to the formula.
VLOOKUPfunction has four attributes,
- First, click on the cell you want to write the formula in. In this example, it would be C3.
- Begin your function with an equal sign
=, then followed by the name of the function,
VLOOKUP, then an open parenthesis
- Next, select the
search_keythat we would like to search, which is A3.
- Then, we will add the
INDIRECTfunction as the
rangewe would like to search from.
- Just like Example 1, we will enter the range we want to search from, which is the
- Lastly, we will insert ‘4‘ as the
index, meaning the column of the value to be returned. The last attribute is optional, but inserting
Falseis recommended to get a more accurate return value.
- The formula is now done! Simply select which branch and month you would like to compare and the profit would automatically show! ✨
You may make a copy of the spreadsheet using the link attached below and try it for yourself:
That’s about it! You can now use the INDIRECT function in Google Sheets together with the other numerous Google Sheets formulas to create even more powerful formulas that can make your life much easier.