The INDIRECT function is generally applied to store a cell reference and use the reference value with other functions to perform multiple operations in the spreadsheets.

The image above is an overview of the application of the INDIRECT function in Excel.

**Introduction to the INDIRECT Function**

**Function Objective:**

**Storing data from the reference specified by a text string.**

**Syntax:**

**=INDIRECT(ref_text, [a1])**

**Arguments Explanation:**

Argument | Compulsory/Optional | Explanation |
---|---|---|

ref_text |
Compulsory |
Reference of a cell with A1 or R1C1 format. |

[a1] |
Optional |
Format of the cell to select- A1 or R1C1Â |

**Return Parameter:**

**The function returns the value(s) present in the stored reference.**

**Example 1 – Using the INDIRECT Function to Convert a Text String into Cell Reference**

In the INDIRECT function, you must input the cell name with column name and row number inside **double quotes(” “)**. See the picture below.

- To find the value of
**B5**, enter the formula in**Â B8**:

`=INDIRECT("B5")`

**Example 2 – Using the Ampersand to Define the Row and Column Index with the INDIRECT Function**

The** Ampersand(&)** can be used to connect column name and row number to reference a cell .

- To find the value of
**C8**, enter the formula in**E7**:

`=INDIRECT("C"&8)`

**Example 3 – Using the ROW Function inside the INDIRECT Function to Define a Cell Reference**

Use **the ROW** **function** to find the row number of a cell.

In the picture below, 3 columns have random numbers. To the value of**Â F7**, define the column name (C) and use the ROW function inside the INDIRECT function to connect the cell to the corresponding row number-7.

- Enter the formula in
**F7**:

`=INDIRECT("C"&ROW())`

**Example 4 – Calculations with Cell References Defined by the INDIRECT Function**

In the following picture, **Column B** contains a range of numbers.

In **D9**, sum the values in **B5:B14**.

- Enter the formula in
**D9**:

`=SUM(INDIRECT("B5:B15"))`

**Method 5 – Locking Cell References with the INDIRECT Function in Excel**

When you input a cell reference or a range of cells inside **double quotes(” “)** in the INDIRECT function, you will have absolute cell references.

- To see the output in
**Â D5**, enter the formula to lock a cell reference in**C5**:

`=B5*INDIRECT("C5")`

- Press
**Enter**and drag down the**Fill Handle**to autofill the rest of the cells.

**Example 6 – Using a Cell Reference from Another Worksheet with the INDIRECT Function**

Use this function to copy a cell or a range of cells from another worksheet.

A chart containing the names of donors and the donation amounts on day 1 is present in **Sheet 1**.

**Sheet 2** represents the chart for day 2.

Use the **INDIRECT **and theÂ **MAX** functions, to find the maximum amount of donations on the first two days in** Sheet 3**.

- Enter the formula in
**D5.**

`=MAX(INDIRECT(C5&"!C5:C9"))`

- Press
**Enter.**

Youâ€™ll get the value for the first day. Apply the formula to the next output cell to see the maximum amount on the second day.

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

**Example 7 – Using a Cell Reference from Another Workbook with the INDIRECT Function**

You can use and copy cell references from another workbook using the INDIRECT function.

The image below showcases the **Donation Data **workbook andÂ the **Day 1** spreadsheet.

To extract the value of**Â C7** to another workbook:

**Step 1:**

- Open a new workbook.
- In
**B4**, enter**â€˜=â€™**and click**C7**in the**Donation Data**workbook.

**Â Step 2:**

- Press
**Enter.**Â ($1200 is displayed) - To keep
**B4**in text format, remove the**Equal(=)**symbol.

In **C6 and C7**, enter the names of the Excel workbook and the spreadsheet from which data will be extracted.

**Step 3:**

- In
**Â C9**, use:

`=INDIRECT("'["&C6&".xlsx]"&C7&"'!$C$7")`

- Press
**Enter**to see the extracted data.

**Example 8 – Using the INDIRECT Function to Refer an Array in the VLOOKUP Function**

In the picture below, two charts of Shop 1 and Shop 2 are displayed.

Extract prices to another chart based on the shops.

Define fish names and prices in the two different shops. Here, **B6:C9** as **Shop_1** and** B13:C16** as** Shop_2.**

In** G9**, extract the price of Tetra in shop 2 by using theÂ **VLOOKUP function.**

The syntax of the **VLOOKUP** function is:

**=VLOOKUP(lookup_value, table_array, col_index_number, [range_lookup])**

- Enter the formula incorporating the
**VLOOKUP**and the**INDIRECT**functions in**G9**:

`=VLOOKUP(E9,INDIRECT("Shop_"&F9),2,FALSE)`

- Press
**Enter**and auto-fill the rest of the cells.

**Example 9 – Using the INDIRECT Function to Refer Another Cell Reference from a Specific Cell**

If you want to store a cell or a range of cells without using double quotes **(â€œ â€œ)**, you have to define cells with column names and row numbers.

In the picture below, **Column B** has random numbers and **Column D **showcases the cell names of those numbers.

- To display the number of
**B11**in the output cell**F10**, enter the following formula:

`=INDIRECT(D11)`

- Press
**Enter**to see the result.

**Example 10 – Referring to a Named Range with the INDIRECT Function in Excel**

The picture below showcases names and the person’s height

Define the name of this entire table.

** Step 1:**

- Select the entire table.
- In the
**Name Box**, enter the name of the table:**Height_data**.

** Step 2:**

- In
**E8**, define the name of the table. - Select a random blank cell. Here,
**G4**and enter:

`=INDIRECT(E8)`

- Press
**Enter**and the entire table will be displayed.

**Example 11 – Transposing a Table with the INDIRECT and the ADDRESS Functions in Excel**

**The ADDRESS** function creates a cell reference as text. The syntax of this function is:

**=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])**

The dataset below showcases name, height, and age.

To transpose this entire table:

** Step 1:**

- Select the output cell
**Â B14**and enter:

`=INDIRECT(ADDRESS(COLUMN(B4) - COLUMN($B$4) + ROW($B$4), ROW(B4) - ROW($B$4) + COLUMN($B$4)))`

- Press
**Enter**and the formula will return**â€˜Nameâ€™.**

** Step 2:**

- Drag the
**Fill Handle**to the right.

** Step 3:**

- Drag down the
**Fill Handle**.

- This is the output.

**How Does This Formula Work?**

The first argument of the **ADDRESS** function, “**COLUMN(B4) – COLUMN($B$4) + ROW($B$4)”** defines the row number by converting the column number of **B4**.

The second argument,** “ROW(B4) – ROW($B$4) + COLUMN($B$4)” **defines the column number by converting the row number of **B4**.

**Read More:** How to Use INDIRECT ADDRESS Functions in ExcelÂ

**Example 12 – Using the INDIRECT Function for Data Validation in Excel**

The dataset showcases 3 smartphone brands, and model names. To create drop-downs for all smartphone brands:

** Step 1:**

- Select
**B5:B10**(model names of**Apple**products). - In the
**Name Box**, define the name of this range of cell: Apple.

** Step 2:**

- In
**B12**, enter Apple. - Select
**Â B12**. - Go to the
**Data**tab and choose**Data Validation**in**Data Tools**.

** Step 3:**

- In
**Allow**, chooseÂ**List**. - In
**Source**, enter:

`=INDIRECT(B12)`

- Â Press
**Enter**to see the result.

- Go to
**Cell B12**and youâ€™ll find the drop-down for all**Apple**products. Follow the same procedure to create drop-downs for the other brands.

## The INDIRECT Function Is Not Working in Excel

### 1. #REF! Error

**#REF!** Error may be displayed in **3 **cases:

**Invalid Cell Reference as ref_text:**check the arguments of the function to make sure cell reference valid.**Exceeding Range Limit:**Excel 2007, 2010, and 2013 have a row limit of 1,048,576 and a column limit of 16,384. If you exceed this limit,**#REF!**Error may occur.**Using Closed Worksheets or Workbooks:**Â Open the worksheets or workbooks before using them in the function.

### 2. #NAME? Error

This error occurs for misspelled functions.

** Things to Keep in Mind**

If you donâ€™t use **double-quotes(â€œ â€œ)** while referring to a cell or a range of cells as a text string, the function will return a **#REF** error.

Unless you define the format of the cell reference in the 2nd argument, the default format will be **A1** style.

**Download Practice Workbook**

Download the Excel workbook.

## Excel INDIRECT Function: Knowledge Hub

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

**<< Go Back toÂ Excel Functions | Learn Excel**