Excel provides various functions to help you complete your analysis tasks effectively. One flexible function is the INDIRECT function, which allows you to refer to cells based on changing criteria, making your spreadsheets more versatile.

This guide will take you through the INDIRECT function, covering its definition, and how to use the function effectively in your spreadsheets.

What Is the INDIRECT Function in Excel?

The INDIRECT function in Excel is used to reference cells, ranges, or named ranges indirectly by using a text string as a reference. Unlike other functions that require direct cell references, the INDIRECT function allows you to create references based on cell contents.

The INDIRECT function in Excel follows a straightforward syntax:

=INDIRECT(ref_text, [A1])

Where:

  • ref_text: This is the required argument and represents the text string that contains a valid cell reference. It can be a cell reference (e.g., A1), a named range (e.g., Prices), or a text string enclosed in quotation marks (e.g., Sheet1!A2).
  • [A1]: This is an optional argument that specifies whether the reference style should be A1 (TRUE) or R1C1 (FALSE). By default, it's set to TRUE.

How to Use the INDIRECT Function in Excel

Here are some examples of how to use the INDIRECT function in Excel:

1. Retrieving Values Based on a Cell Reference

You can use INDIRECT with the MATCH function in Excel to fetch a value from a range based on a cell reference. Here's an example:

Suppose you have a list of items in column A (A2:A8) and their corresponding prices in column B (B2:B8). In cell D2, you have the item name you want to fetch the price for, let's say Apples.

To fetch the price for the item "Apples" using the INDIRECT function combined with the MATCH function, you can use the following formula:

=INDIRECT("B" & MATCH(D2, A2:A8, 0))
Retrieving corresponding values using Excel's INDIRECT function
Screenshot by Bolanle Abiodun - No attribution required

In this formula:

  • MATCH(D2, A2:A8, 0) finds the row number where Apples is located in the list.
  • "B" & MATCH(D2, A2:A8, 0) combines "B" with the row number to create the reference "B5" (assuming "Apples" is in cell A5).
  • Finally, INDIRECT("B5") fetches the value from cell B5, which is the price for Apples.

2. Calculating the Sum of a Range of Cells

Suppose you have a sheet with different values in cells A1 to A7. You can calculate the sum of the range using the INDIRECT function. Here's the formula you'll use:

=SUM(INDIRECT("A1:A7"))
Adding a range of cells in Excel using the INDIRECT function
Screenshot by Bolanle Abiodun - No attribution required

This formula uses the INDIRECT function to refer to the range (A1:A7) and then uses the SUM function to calculate the sum of the values within that range.

3. Referencing the Named Ranges

If you have defined a named range, you can use the INDIRECT function to reference and perform calculations on the range.

Suppose you have a dataset containing sales figures for different months. We'll use the INDIRECT function to get the average sales for each week of the month.

=AVERAGE(INDIRECT(F2))
Example using INDIRECT to refer to a named range in Excel
Screenshot by Bolanle Abiodun - No attribution required

This formula references the named range specified in cell F2 (like Week1, which refers to cells B2:B6) and calculates the average of the values within that range.

4. Referencing Cells From Another Sheet

Suppose you have a workbook with two sheets: CityData and Report. The CityData sheet contains information about different cities. In the Report sheet, you want to create a summary that references data from the CityData sheet, such as the population of Kinshasa.

Sample sheet showing various cities' information
Screenshot by Bolanle Abiodun - No attribution required

Enter the following formula in any cell in the Report sheet:

=INDIRECT("CityData!C6")
Sample sheet referencing cells from another sheet
Screenshot by Bolanle Abiodun - No attribution required

This formula will display the population of Kinshasa from cell C6 in the CityData sheet. You can also adjust the cell references within the INDIRECT function to retrieve data from the CityData sheet for different cities or information types.

Flexible Data Referencing With Excel's INDIRECT Function

The INDIRECT function in Excel is a versatile tool that allows you to create dynamic and flexible references within your spreadsheets. By understanding its syntax and applications, you can maximize it and elevate your data manipulation and analysis skills. Whether you're working on complex reports or consolidating data, the INDIRECT function is a key resource in your Excel arsenal.