Direct vs indirect referencing

The INDIRECT function

The Excel INDIRECT function returns a reference specified by a text string.

SYNTAX: INDIRECT(ref_text, [a1])

Figure 1 shows examples of the INDIRECT function (two stage), and a direct reference (one stage). The custom GetCF function is used to show the cell formula.

Fig 1: Direct reference and Indirect reference - cell D5 contains a direct reference, cell D11 contains an indirect reference via cell F7 that points to cell B8

When to use the INDIRECT function

The INDIRECT function is often used in conjunction with the concatenation operator (&) to construct references from labels. For example =VLOOKUP($A14,INDIRECT(B$13&".ax!"&B$13),8,FALSE) from the link at xlf Vlookup w/ indirect

INDIRECT allows R1C1 reference style in A1 workbook

Using the INDIRECT function is one way of using R1C1 reference style in an A1 workbook, or A1 reference style in an R1C1 workbook.

Fig 2: INDIRECT function - with A1 argument set to False. This allows R1C1 style in the A1 workbook

Download the file: xlf-indirect-function.xlsx [12 KB]