INDIRECT Function

  • Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Starter 2010.

This article outlines the formula syntax and usage of the INDIRECT function in Microsoft Excel.

INDIRECT Function example.
© Exceljet, 2020.

INDIRECT Function Description

Returns the reference clarified by a text string. References are instantly evaluated to show their contents. Use INDIRECT when you seek to alter the reference to a cell inside a formula and avoid editing any aspect of the actual formula.

INDIRECT Function Syntax

INDIRECT(ref_text, [a1])

The INDIRECT function syntax contains these listed arguments:

  • Ref_text – Required. A reference to a cell with an A1-style reference, an R1C1-style reference, a name set as a reference, or a reference to a cell as a text string. If ref_text is an invalid cell reference, INDIRECT captures the #REF! error value.
    • If ref_text refers to a different workbook (an external reference), the other workbook has to be open as well. If the source workbook is closed, then INDIRECT returns the #REF! error value.

      Note – External references are unsupported in Excel Web App.
    • If ref_text refers to a cell range beyond the row cap of 1,048,576 or the column limit of 16,384 (XFD), INDIRECT brings back a #REF! error.

      Note – This behaviour contrasts from Excel versions prior to Microsoft Office Excel 2007, which overlook the surpassed restriction and return a value.
  • A1  – Optional. A logical value that defines what kind of reference is included in the cell ref_text.
    • If a1 is TRUE or ignored, ref_text is discerned as an A1-style reference.
    • If a1 is FALSE, ref_text is considered as an R1C1-style reference.

INDIRECT Function Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to display results, choose them, press F2, and then press Enter. If you only have a partial view of your dataset then you can extend your current column widths to see all the data.

Data
B21.333
B345
George10
562
FormulaDescriptionResult
‘=INDIRECT(A2)Value of the reference in cell A2. The reference is to cell B2, which has the value 1.333.1.333
‘=INDIRECT(A3)Value of the reference in cell A3. The reference is to cell B3, which includes the value 45.45
‘=INDIRECT(A4)Because cell B4 has the specified name “George,” the reference to that defined name is to cell B4, which has the value 10.10
‘=INDIRECT(“B”&A5)Merges “B” with the value in A5, which is 5. This gradually refers to cell B5, which includes the value 62.62

Also Check Out

VLOOKUP Function

HLOOKUP Function

CHOOSE Function

MATCH Function

INDEX Function

Look up values in a list of data

Leave a Reply

%d bloggers like this: