How to Use the INDIRECT Function in Excel

Welcome to this detailed guide on using the INDIRECT function in both Microsoft Excel and Google Sheets. The INDIRECT function is extremely useful for referring indirectly to cells in a spreadsheet. It is especially helpful when dealing with cell or cell range references defined by text strings.

Basic Syntax

The syntax for the INDIRECT function is as follows:

=INDIRECT(ref_text, [a1])

ref_text: This mandatory argument specifies the cell reference as a text string, which can either be a straightforward cell address like “A1” or a named range.
[a1]: This optional argument determines the style of the reference. If set to TRUE or omitted, the A1 reference style is used, where columns are labeled with letters and rows with numbers. If FALSE, it uses R1C1 reference style, where both columns and rows are labeled numerically.

Dynamic Referencing

One prevalent use of the INDIRECT function is dynamic cell referencing. Suppose you have a dropdown menu listing column headers and you need to fetch data from a selected column dynamically. The INDIRECT function facilitates this seamlessly.

A	B	C
Header 1	Header 2	Header 3
100	200	300

Example: If cell E1 contains the letter “B”, you can utilize the following formula to fetch the value from cell B2:

=INDIRECT(E1 & "2")

This formula indirectly references cell B2 and displays the value 200.

Sum of a Range

The INDIRECT function also proves effective in calculating the sum of a range based on user input or certain conditions.

A	B	C
10	20	30

Example: If cell E1 contains the text “A:C”, you can use the following formula to calculate the sum of the range A1:C1:

=SUM(INDIRECT(E1))

This formula will indirectly reference the range A1:C1 and return the total 60.

These examples illustrate just a fraction of the potential applications for the INDIRECT function in enhancing the dynamics and flexibility of formulas in your Excel and Google Sheets documents. Explore various scenarios to fully exploit the capabilities of this function!

More information: https://support.microsoft.com/en-us/office/indirect-function-474b3a3a-8a26-4f44-b491-92b6306fa261

Other functions

ADDRESS

Returns a reference as text to a single cell in a worksheet

AREAS

Returns the number of areas in a reference

CHOOSE

Chooses a value from a list of values

COLUMN

Returns the column number of a reference

COLUMNS

Returns the number of columns in a reference

FILTER

Filters a range of data based on criteria you define

FORMULATEXT

Returns the formula at the given reference as text

GETPIVOTDATA

Returns data stored in a PivotTable report

HLOOKUP

Looks in the top row of an array and returns the value of the indicated cell

HYPERLINK

Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet

INDEX

Uses an index to choose a value from a reference or array

LOOKUP

Looks up values in a vector or array

MATCH

Looks up values in a reference or array

OFFSET

Returns a reference offset from a given reference

ROW

Returns the row number of a reference

ROWS

Returns the number of rows in a reference

RTD

Retrieves real-time data from a program that supports COM automation

SORT

Sorts the contents of a range or array

SORTBY

Sorts the contents of a range or array based on the values in a corresponding range or array

TRANSPOSE

Returns the transpose of an array

UNIQUE

Returns a list of unique values in a list or range

VLOOKUP

Looks in the first column of an array and moves across the row to return the value of a cell

XLOOKUP

Searches a range or an array, and returns an item corresponding to the first match it finds If a match doesn't exist, then XLOOKUP can return the closest (approximate) match

XMATCH

Returns the relative position of an item in an array or range of cells