How to Use Indirect Function in Excel

The Indirect function in Excel retrieves a reference identified by a text string. It evaluates these references and displays their contents, offering a way to change a cell reference within a formula without directly altering the formula itself.

Parameters of Indirect function

Traditionally, the Indirect function requires two parameters: the reference text and the A1 style.

  • Reference Text: This specifies the reference to a cell in the A1 style or the R1C1 style. If the ref_text is an invalid cell reference, it returns an #REF! error.
  • A1: This logical value determines how the ref_text in the cell is interpreted. When set to true, it reads the ref_text as an A1 style reference. If false, it interprets the ref_text as an R1C1 style reference.

Examples of Indirect function

Simple Indirect Function

Utilizing Indirect to extract a value from a specific column, providing a method to retrieve information from cell A4.

Using Simple Indirect Function

Sum and Indirect Function

Using the Sum function in conjunction with Indirect to compute the total of a range, showcasing a way to gather a specific number from a set of values.

Using Sum and Indirect Function

Average with Indirect Function

Determining the average performance of a company, particularly in sales. Using Indirect helps find the average value, crucial for assessing overall company performance.

See also  How to Calculate Median in Excel

Using Average with Indirect Function

Additional Sum and Indirect Function Usage

Summing multiple numbers while utilizing the Indirect function, requiring careful placement of data in separate columns for effective formula execution.

Additionally Sum and Indirect Function Usage

Indirect Function with Text

A variation using only the Indirect function without the Sum function to attain desired information.

Indirect Function with Text

Sum and Indirect Extended

Analyzing business sales over specific months by using Indirect to compute values between starting and ending months, offering insights into business performance within that timeframe.

Sum and Indirect Extended

Finding a Specific Value with Indirect & Row Function

Isolating data for a particular month, in this case, June, using Indirect to extract specific values from a dataset.

Finding a Specific Value with Indirect Row Function

Indirectly Finding a Number in a Column

Locating a specific number within a column by employing the Indirect function, exemplifying how to find targeted data within a dataset.

Indirectly Finding a Number in a Column

Knowing Sales with SUM and INDIRECT Function

Using the Sum and Indirect functions to assess sales in various areas of a business, aiming to gauge overall performance, particularly for the Ultimate Provider, a business specializing in broadband and telephone services.

Knowing Sales with SUM and INDIRECT Function

Finding Sales from another Spreadsheet

Exploring how the Indirect function can retrieve information from a different spreadsheet, showcasing its utility in gathering data from external sources.

Finding Sales from another Spreadsheet