# How to Use INDIRECT Function in Google Sheets

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.

#### Let’s take an example:

By selecting A2, the return value would be “123”, as it is the cell reference in A4.

The `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.

The `INDIRECT` function would be really handy in real-world situations!

## The Anatomy of the INDIRECT Function

The way we write the `INDIRECT` Function is:

`=INDIRECT(cell_reference_as_string, [is_A1_notation]) `

• The equal sign `=`  is how we start any function in Google Sheets.
• `INDIRECT()` is our function. We need to add two attributes, namely the `cell_reference_as_string` and `[is_A1_notation]`, to make it work correctly.
• The `cell_reference_as_string` is a cell reference, written as a string.
• The  `[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]` means.

#### 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

#### Example 1

Let’s use a real-life situation to utilize the `INDIRECT`function 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

1. Simply click on the cell that you want to write down your function at. In this example, it will be B2.

1. Begin your function with an equal sign `=`, followed by the name of the function, `SUM`, and an open parenthesis`(`.

1. Add `INDIRECT`, then another open parenthesis`(`.

1. We will select A2 as our `cell_reference_as_string`.

By selecting A2, we are referring the formula to the tab named ‘Jan‘.

1. Then, add an ampersand`&` to join two or more text strings in a single string.

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

1. After the following steps, your input should look like this:

#### Example 2

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:

1. 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.
2. `VLOOKUP` function – helps pull out data from a complex database or tables.

For more in-depth explanations and examples for the `VLOOKUP` function, click here!

1. To create data validation on a cell. First, select the cell you want to validate, which is A3.

1. Next, click on ‘Data‘ on the Google Sheet Toolbar.

1. Select ‘Data Validation‘.

1. Once clicked, this is what would display on your screen:

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

1. After selecting the range of data, click Save.

1. After clicking Save, the cell would display an arrow that would drop down to a list of branch names.

1. Do the same for the “Month” column.

You can also manually list down the items.

1. Now let’s move on to the formula. `VLOOKUP` function has four attributes, `search_key`, `range`, `index`, and `[is_sorted]`.

1. First, click on the cell you want to write the formula in. In this example, it would be C3.

1. Begin your function with an equal sign `=`, then followed by the name of the function, `VLOOKUP`, then an open parenthesis`(`.

1. Next, select the `search_key` that we would like to search, which is A3.

1. Then, we will add the `INDIRECT` function as the `range` we would like to search from.

1. Just like Example 1, we will enter the range we want to search from, which is the `cell_reference_as_string` attribute.

1. Lastly, we will insert ‘4‘ as the `index`, meaning the column of the value to be returned. The last attribute is optional, but inserting `0` or `False` is recommended to get a more accurate return value.

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

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

## How to Use COUNTIF Function in Google Sheets

The COUNTIF function in Google Sheets is useful if you want to count the number of times a…

## How to Use the ACCRINT Function in Google Sheets

The ACCRINT function in Google Sheets is designed specifically to calculate the accrued interest for an investment or…

## How to Use ISDATE Function in Google Sheets

The ISDATE function in Google Sheets is useful if you want to know whether the value in a…

## How to Get NYSE Real-Time Stock Prices in Google Sheets

Learning how to obtain prices from the NYSE market in Google Sheets is useful for when you need…