How to Use the INDIRECT Function in Excel – 12 Examples

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:

• 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:

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:

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:

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:

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:

• 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.

• 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:

• 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:

• 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:

• 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:

• 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:

• 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.

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:

•  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