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:
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 theMATCH function in Excelto 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 sayApples.
To fetch the price for the item “Apples” using the INDIRECT function combined with the MATCH function, you may use the following formula:
In this formula:
2. Calculating the Sum of a Range of Cells
Suppose you have a sheet with different values in cells A1 to A7. you may calculate the sum of the range using the INDIRECT function. Here’s the formula you’ll use:
This formula uses the INDIRECT function to refer to the range (A1:A7) and then uses theSUM functionto 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.
This formula references the named range specified in cell F2 (likeWeek1, 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:CityDataandReport. TheCityDatasheet contains information about different cities. In theReportsheet, you want to create a summary that references data from theCityDatasheet, such as the population of Kinshasa.
Enter the following formula in any cell in the Report sheet:
This formula will display the population of Kinshasa from cell C6 in theCityDatasheet. You can also adjust the cell references within the INDIRECT function to retrieve data from theCityDatasheet 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’re able to 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.
