Excel Indirect Function

This post will guide you how to use Excel INDIRECT function with syntax and examples in Microsoft excel.

Description

The Excel INDIRECT function returns the cell reference based on a text string, such as: type the text string “A2” in B1 cell, it just a text string, so you can use INDIRECT function to convert text string as cell reference.

The INDIRECT function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.

The INDIRECT function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the INDIRECT function is as below:

= INDIRECT  (ref_text,[a1])

Where the INDIRECT function arguments are:
Ref_text -This is a required argument. A reference to a cell as a text string.
A1 – This is an optional argument.  It is a logical value that specify the style of the Ref_text reference.
Note: If A1 value is True or omitted, the ref_text will be interpreted as an A1-style reference.
If A1 value is False, The ref_text will be interpreted as R1C1 style reference.

Example

The below examples will show you how to use Excel INDIRECT Lookup and Reference Function to return a  cell or range reference that is represented by a text string.

#1 To get the value of the reference in cell A2, just using the following excel formula: =INDIRECT(A2)

excel indirect function example1

More Excel INDIRECT  Function Examples


  • Get Cell Value Based on Row and Column Numbers
    If you want to retrieve values using cell references based on row and column numbers, you can create a formula based on the INDIRECT function and the ADDRESS function.….
  • Sumif with 3D Reference for Multiple Worksheet
    If you want to sum 3d references across the different worksheet, and you need to create a complex formula based on the SUMPRODUCT function, the SUMIF function, and the INDIRECT function..….
  • Sum Numbers from 1 to N
    Assuming that you supply a number 100, and you want to sum the numbers from 1 to 100 (1+2+3+…+100), you need to create a formula based on the SUMPRODUCT function, the ROW function and the INDIRECT function to achieve the result..….
Related Posts

How To Sum the Largest N Values in Excel

Sometimes we may want to sum the largest N numbers or top N numbers in a range. In this article, we will show you the method of “SUM the Largest N Numbers” by a simple formula which consist of SUMPRODUCT ...

How to Sum the Smallest N Values in Excel
How to Sum the Smallest N Values in Excel 15

Sometimes we may want to sum the first smallest N numbers in a range in Excel. In this article, we will show you the method of “SUM the Smallest N Numbers” by a simple formula which consist of SUMPRODUCT and ...

Count Non-Contiguous Range using COUNTIF
count non-contiguous ranges 2

This post will guide you how to count a non-contiguous range with criteria in COUNTIF function in Excel 2013/2016 or Excel office 365. How do I build a formula for counting non-contiguous range in Excel using COUNTIF function in Excel. ...

How to Count Occurrences in Entire Workbook in Excel
count occurrences in entire workbook7

This post will guide you how to count number of occurrences in whole workbook in Excel 2013/2016 or Excel office 365. How do I count how many times a string is repeated in all worksheets in Excel. You may be ...

How to Remove Numbers from Text in Excel
remove numbers from text2

This post will guide you how to remove all numbers from a text string in one cell in Excel. How do I strip all numeric characters from a given cell with a formula in Excel. How do I remove numbers ...

How to convert Column Letter to Number in Excel
convert column letter to number8

This post will guide you how to convert column letter to number in Excel. How do I convert letter to number with a formula in Excel. How to convert column letter to number with VBA macro in Excel. How to ...

How to Copy Value of Same cell From Multiple Worksheets in Excel
copy value of same cell from multiple sheets4

This post will guide you how to copy value from same cell on multiple worksheets in your workbook in Excel. How do I List values of same cell reference from multiple worksheets with a formula in Excel. How to copy ...

Countif across Multiple Worksheets
count values accross multiple sheets2

This post will guide you count a value across multiple worksheets in excel. How do I use COUNTIF function to get the number of occurrence of a value from multiple worksheets in Excel. How to count the number of values ...

Reverse Text String in Excel
reverse text string 2

This post will guide you how to reverse text string in Excel. How do I reverse text string in one cell with an Excel User Defined Function. How to reverse a text string in one cell with a formula in ...

Removing Non Numeric Characters
remove non numeric characters2

This post will guide you how to remove non numeric characters from cells with a formula in Excel. How do I remove non-numeric characters in cells in Excel. How to remove non-numeric characters from cells with VBA Code in Excel. ...

Sidebar