How to use the INDIRECT formula in Google Sheets
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.
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.
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 RC style of address notation.
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!
The vice versa is also true, as indicated in the snapshot below.
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
Tip: learn more about another useful formula in Google Sheets in the following blog: How to use the ROW formula in Google Sheets.