INDIRECT function in Excel

Function INDIRECT (INDIRECT) returns the link specified by the text string.

  1. For example, the function INDIRECT (INDIRECT) in the figure below boils down to:

    =INDIRECT(B1) ► =INDIRECT("С2") ► =C2 ► 5

    =ДВССЫЛ(B1) ► =ДВССЫЛ("С2") ► =C2 ► 5

    Do we really need a function for this? INDIRECT (INDIRECT)? Yes! In the figure below you can see the result without using the function INDIRECT (INDIRECT):

  2. For example, the formula in the following figure boils down to:

    =SUM(INDIRECT("E"&A1&":E"&A2)) ► =SUM(INDIRECT("E3:E6")) ► =SUM(Е3:E6) ► 27

    =СУММ(ДВССЫЛ("E"&A1&":E"&A2)) ► =СУММ(ДВССЫЛ("E3:E6")) ► =СУММ(Е3:E6) ► 27

Note: The concatenation operator (&) is used to concatenate strings. The border in the picture above is for illustration purposes only.

  1. One more example. The formula below boils down to:

    =AVERAGE(INDIRECT(B1)) ► =AVERAGE(Scores) ► 9

    =СРЗНАЧ(ДВССЫЛ(B1)) ► =СРЗНАЧ(Scores) ► 9

Note: Named range scores is the range C2: C4.

Leave a Reply