The **INDIRECT **function (a **Lookup & Reference** function) is used to return the reference specified by the text string. This function is used to get values from any reference of a cell or a range. In this article, I’m going to explain the use of the Excel **INDIRECT** range.

To make the explanation clearer, I’m going to use a dataset that represents sales information for 4 quarters. The dataset has 5 columns these are * Sales Person, Quarter-1, Quarter-2, Quarter-3, *and

*.*

**Quarter-4****Table of Contents**hide

## Download to Practice

## 8 Ways to Use Excel INDIRECT Range

### 1. Using INDIRECT Range to Get Value Through Cell Reference

You can use the **INDIRECT** function to fetch value through the cell reference of a cell or cell range.

Here, I want to get the values of the cell range **C4:C8** from **Quarter-1**.

To do it,

⏩ In cell **H4**, type the following formula.

`=INDIRECT("C4:C8")`

Here, in the **INDIRECT **function, I used the cell range **C4:C8 **as a **string/text (“C4:C8”)** in **ref_text**.

Now, press **ENTER**, and you will get all the values of the used cell references.

### 2. Get Value Through Cell Reference in Cell

In case you want to fetch values while keeping the cell or cell range in a cell then you also can do it by using the **INDIRECT** function.

Here, I want to get the values of the cell range **D4:D8** from **Quarter-2**. Instead of using the cell reference directly, I want to keep it in a cell. Here, in the dataset given below, I place the cell range from where I want to fetch the value.

⏩ In cell **I4**, type the following formula.

`=INDIRECT(H4)`

Here, in the **INDIRECT **function, I selected cell **H4 **as **ref_text**. Where I stored the cell range **D4:D8** in the **H4 **cell. So, it will fetch the values of those cells.

Now, press **ENTER**, and you will get all the values from the selected cell.

**3. Using Value in a Cell Creating Reference**

The **INDIRECT **function gives us the opportunity to create a cell reference using a value in a cell.

Let me show you the process of creating a reference.

⏩ In cell **I4**, type the following formula.

`=INDIRECT("E"&H4)`

Here, in the **INDIRECT **function, I used **“E”&H4 **as **ref_text**. Where **E **is the column and in **H4 **I stored the value **5**. In the function, I concatenated column **E **and the value of the **H4 **cell so that it will become **E5**. Finally, the **INDIRECT** function will fetch the value of the **E5 **cell.

Now, press **ENTER**, and you will get the values from the cell you used as a reference.

**4. Find Out the SUM of a Range of Cells Using Named Range**

You can use the **named range** in the **INDIRECT **function. Then by using other formulas you can calculate the values of the **named range**.

Let me show you the process of naming any range,

First, select the cell range.

➤ I selected the cell range **C4:C8**.

Then, go to the **Address Bar** and type any name of your choice to name the range.

➤ I named the selected cell **Quarter_1**.

Now, to use the named range to calculate total sales of **Quarter_1**, I’m going to use the **INDIRECT **and the **SUM **function together.

⏩ In cell **I4**, type the following formula.

`=SUM(INDIRECT(I3))`

Here, in the **SUM **function, I used **INDIRECT(I3) **as a **number1**.

In the **INDIRECT **function, I selected cell **I3 **as **ref_text**. Where in **I3 **I kept the name of the range **C4:C8**.

Now, the **INDIRECT **function will get the values of those cells and the **SUM **function will return the total of those cells.

Finally, press **ENTER**, and you will get total sales of **Quarter_1**.

**5. Using INDIRECT Named Range to Find The AVERAGE **

You also can calculate the **AVERAGE **of a **named range** of cells using the **AVERAGE **function and the **INDIRECT **function.

⏩ In cell **I4**, type the following formula.

`=AVERAGE(INDIRECT(I3))`

Here, in the **AVERAGE **function, I used **INDIRECT(I3) **as a **number1**.

In the **INDIRECT **function, I selected cell **I3 **as **ref_text**. Where in **I3 **I kept the name of the range **C4:C8**.

Now, the **INDIRECT **function will get the values of those cells and the **SUM **function will return the **average** of those cells.

Finally, press **ENTER**, and you will get **average** sales of **Quarter_1**.

**6. Find Out The MAXIMUM of a Range of Cells**

In case you want to find out the maximum of a named range for that you can use the **MAX **function and the **INDIRECT **function.

⏩ In cell **I4**, type the following formula.

`=MAX(INDIRECT(I3))`

Here, in the **MAX **function, I used **INDIRECT(I3) **as a **number1**.

In the **INDIRECT **function, I selected cell **I3 **as **ref_text**. Where in **I3 **I kept the name of the range **C4:C8**.

Now, the **INDIRECT **function will get the values of those cells and the **MAX **function will return the **maximum** number from those cells.

Finally, press **ENTER**, and you will get the **maximum** sales of **Quarter_1**.

**7. Find Out The MINIMUM of Range of Cells**

If you want, you also can find out the minimum of a named range by using the **MIN **function and the **INDIRECT **function.

⏩ In cell **I4**, type the following formula.

`=MIN(INDIRECT(I3))`

Here, in the **MIN **function, I used **INDIRECT(I3) **as a **number1**.

In the **INDIRECT **function, I selected cell **I3 **as **ref_text**, wherein **I3 **I kept the name of the range **C4:C8**.

Now, the **INDIRECT **function will get the values of those cells and the **MIN **function will return the **minimum** number from those cells.

Press **ENTER **and you will get the **minimum** sales of **Quarter_1**.

**8. Using Excel INDIRECT Range to Find The AVERAGE of SMALL Values**

You also can find out the **average** of the **3 smallest** numbers from a range of cells by using the **AVERAGE **function, **SMALL** function, **ROW **function, and the **INDIRECT **function.

⏩ In cell **I4**, type the following formula.

`=AVERAGE(SMALL(F4:F8,ROW(INDIRECT("1:3"))))`

Here, in the **AVERAGE **function, I used **SMALL(F4:F8,ROW(INDIRECT(“1:3”))) **as a **number1**.

In the **SMALL **function, I used **F4:F8** as **array **and **ROW(INDIRECT(“1:3”))** as **k** which is position.

Then, in the **ROW **function, I used **INDIRECT(“1:3”)** as a **reference**.

In the **INDIRECT **function, I used **1:3 **as **ref_text**. Where **1:3 **means **3 **values. Here, the **INDIRECT **function is used in the **ROW **function so that the formula remains correct even if you insert or delete any rows.

Now, the **SMALL **function will return the **3 **smallest numbers and the **AVERAGE **function will return the average of those smallest numbers.

Press **ENTER **and you will get the **average **of **3 smallest** sales of **Quarter_4**.

## Things to Remember

🔺 The **INDIRECT** function shows the **#REF** error if **ref_text** is not a valid cell reference and the **range limit** is exceeded.

🔺 The **INDIRECT **function will show the **#NAME **error if you misspell the function name.

## Practice Section

## Conclusion

In this article, I have explained 8 ways to use Excel **INDIRECT **range. I also tried to cover the when and why the **INDIRECT** function may show errors frequently. Last but not least, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.