How to use the INDIRECT formula in Google Sheets

featured image indirect formula arrow

Written by Valentine Schelstraete

Mar 30, 2017

The INDIRECT formula in Google Sheets takes in the cell address in the form of text and returns a cell reference. It works the opposite as that of the ADDRESSformula, that returns an address in the text form.

Syntax

INDIRECT(cell_reference_as_string, [is_A1_notation])

cell_reference_as_string – is the text form of a cell address. Notice that it is not the cell reference itself.

is_a1_notation – there are two kinds of representations for a cell address. One in the form of A1. And the other way of representing the same cell is R1C1 (just a short representation for row # 1 and column # 1). TRUE is the value by default, and we get A1 notation in return. Otherwise, if we need R1C1 notation, we need to specify FALSE.

Usage: INDIRECT formula

We understand the concept better with help of examples. So, here are a few combinations of the formula.

INDIRECT Formula: 7 Combinations of The Formula

You’ll notice that the first parameter can be a direct string enclosed in double quotes (rows 2 and 3), a cell reference that holds the address string (row # 7), or even a concatenated string (rows 4 and 5).

Please note that the INDIRECT formula cannot handle R[1]C[1] style of address notation. Instead it can process R1C1 style. So, in case we have to process the former representation, we can work around it like we did in the row # 8. We substituted the square brackets with empty strings using the SUBSTITUTE formula.

Will the INDIRECT formula result in errors? Yes, of course!

The following screenshot illustrates the outcome when we try to input A1 style address notation for cell_reference_as_string and indicate FALSE for the is_A1_notation parameter. Doing this will obviously leave the INDIRECT formula looking for R1C1 style notation, and it doesn’t find one. Hence the error!

INDIRECT Formula: Snapshot of The Error in Google Sheets

The vice versa is also true, as indicated in the snapshot below.

INDIRECT Formula in Google Sheets

Now the worst case scenario. We try and input a text form that is neither A1 representation nor the R1C1 representation, but just some dummy text. And, of course it should throw up an error!

INDIRECT Formula: Snapshot of the Error

Tip: learn more about another useful formula in Google Sheets in the following blog: How to use the ROW formula in Google Sheets.

You may also like…

Share This