To explain the INDIRECT function, we’ll use a dataset that represents sales information for 4 quarters. The dataset has 5 columns: Salesperson, Quarter-1, Quarter-2, Quarter-3, and Quarter-4.

## How to Use the Excel INDIRECT Range: 8 Easiest Ways

### Method 1 – Using the INDIRECT Range to Get a Value Through a Cell Reference

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

- In cell
**H4**, insert the following formula:

`=INDIRECT("C4:C8")`

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

- Press
**Enter**and you will get all the values of the used cell references.

### Method 2 – Get a Value Through a Cell Reference in a Cell

Let’s get the values of the cell range **D4:D8** from **Quarter-2**. Instead of using the cell reference directly, we want to keep it in a cell. In the dataset given below, we placed the cell range in a separate cell.

- In cell
**I4**, insert the following formula:

`=INDIRECT(H4)`

In the **INDIRECT **function, we selected cell **H4 **as **ref_text**. This is where we stored the cell range **D4:D8** in the **H4 **cell, so it will fetch the values of those cells.

- Press
**Enter**.

**Read More:** How to Use the INDIRECT Function to Get Values from Different Sheet in Excel

**Method 3 – Using a Value in a Cell for Creating a Reference**

- In cell
**I4**, use the following formula.

`=INDIRECT("E"&H4)`

Here, in the **INDIRECT **function, we used **“E”&H4 **as **ref_text**. **E **is the column and in **H4 **we put 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.

- Press
**ENTER**and you will get the values from the cell you used as a reference.

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

- Select a cell range. We selected the cell range
**C4:C8**. - Go to the
**Address Bar**and type any name of your choice to name the range. We named the selected range**Quarter_1**.

- In cell
**I4**, use the following formula:

`=SUM(INDIRECT(I3))`

Here, in the **SUM **function, we used **INDIRECT(I3) **as a **number1**. In the **INDIRECT **function, we selected cell **I3 **as **ref_text,** where we put the name of the range **C4:C8**. The **INDIRECT **function will get the values of those cells and the **SUM **function will return the total of those cells.

- Press
**Enter**and you will get total sales of**Quarter_1**.

**Method 5 – Using the INDIRECT with a Named Range to Find the AVERAGE **

- In cell
**I4**, use the following formula:

`=AVERAGE(INDIRECT(I3))`

In the **AVERAGE **function, we used **INDIRECT(I3) **as a **number1**. For the **INDIRECT **function, we selected cell **I3 **as **ref_text, **which contains the name of the range **C4:C8**. The **INDIRECT **function will get the values of those cells and the **SUM **function will return the **average** of those cells.

- Press
**Enter**and you will get the**average**sales of**Quarter_1**.

**Method 6 – Find the MAXIMUM of a Range of Cells**

- In cell
**I4**, use the following formula:

`=MAX(INDIRECT(I3))`

In the **MAX **function, we used **INDIRECT(I3) **as a **number1**. For the **INDIRECT **function, we used cell **I3 **as **ref_text,** which contains the name of the range **C4:C8**. The **INDIRECT **function will get the values of those cells and the **MAX **function will return the **maximum** number from those cells.

- Press
**Enter**and you will get the**maximum**sales of**Quarter_1**.

**Method 7 – Find Out the MINIMUM of Range of Cells**

- In cell
**I4**, use the following formula:

`=MIN(INDIRECT(I3))`

In the **MIN **function, we used **INDIRECT(I3) **as a **number1**. For the **INDIRECT **function, we selected cell **I3 **as **ref_text**, which holds the name of the range **C4:C8**. 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**.

**Method 8 – Using the Excel INDIRECT Range to Find the AVERAGE of SMALL Values**

- In cell
**I4**, copy the following formula:

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

In the **AVERAGE **function, we used **SMALL(F4:F8,ROW(INDIRECT(“1:3”))) **as a **number1**. For the **SMALL **function, we used **F4:F8** as **array **and **ROW(INDIRECT(“1:3”))** as **k** which is position. In the **ROW **function, we used **INDIRECT(“1:3”)** as a **reference**. In the **INDIRECT **function, we used **1:3 **as **ref_text** so it fetches **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. 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

**Download to Practice**

## Related Articles

- How to Use Indirect Address Functions in Excel
- INDIRECT Function with Sheet Name in Excel
- Create Drop-Down List Using INDIRECT Function in Excel
- How to Convert Text to Formula Using the INDIRECT Function in Excel

**<< Go Back to Excel INDIRECT Function | Excel Functions | Learn Excel**