INDIRECT Function in Excel with Few Examples

This function is has a good usage in dynamic range for summing up, for average and so on.

There is no need to directly select the cells range we can only give the cell reference which shows the numbers or cell reference.

Summary 

The Excel INDIRECT function returns a valid reference from a given text string. Use INDIRECT when you need to convert a reference assembled as text into a proper reference.

Purpose 

Create a reference from text.

Return value 

A valid worksheet reference.

Syntax 

=INDIRECT (ref_text, [a1])

Arguments 

ref_text - A reference supplied as text.

a1 - [optional] A boolean to indicate A1 or R1C1-style reference. Default is TRUE = A1 style.

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

1.       For example, the INDIRECT function below reduces to =INDIRECT("B2"), =B2, 5

This is very useful function for dynamic range. Below you can find the result without using the INDIRECT function.

2.       For example, the function below reduces to =SUM(INDIRECT("A3:A5")), =SUM(A3:A5), 60

3.       For example, the function below reduces to ==AVERAGE(INDIRECT("A"&B1&":A"&B2)), 20