How to use INDIRECT function in Excel
In this blog post, we will try to understand about INDIRECT function of excel. The excel INDIRECT function returns a reference to a range. We can use this function to create a reference that won’t change if rows or columns are inserted in the worksheet. Or, cause it to create a reference from letters and numbers in other cells.
The INDIRECT function in excel has two arguments as seen in the below picture:
- ref_text: A cell reference or text string (or both), that creates the range reference. The referenced range can be a cell, a range of cells, or a named range.
- a1: TRUE or FALSE. Does the reference use A1 reference style? If this argument is TRUE or omitted, the ref_text is A1 style. If the argument is FALSE, the ref_text is R1C1 style.
If the INDIRECT formula refers to a different workbook, that workbook must be open otherwise the formula will return #REF! error.
Implementation and use case:
If we create a simple link to a cell, e.g. =A3 in the below image, and we insert a row above row 3, our formula will automatically change to =A4. To prevent this change, we can use the INDIRECT function.
To demonstrate the difference, we’ll create two formulas –> one that uses a simple link and one that uses a text string with an INDIRECT function in excel.
- In cell A3, type a number (505 in the above example).
- In cell B2, type the following formula:
- Press the Enter key, and the formula will return the number in cell A3.
- In cell C2, type the following formula:
- Press the Enter key, and the formula will return the number in cell A3 same as the above example.
- To insert a row, right-click on the Row 3 button, and from the pop-up menu, choose Insert.
- As shown below, cell B2, which uses the INDIRECT function, now returns 0, because cell A4 is empty. The text string, “A3”, in the INDIRECT formula did not change when the row was inserted.
- Cell C2, which contains the link, still returns 505, because its formula has changed to =A4.
Create a Reference from a Cell Value and Text:
In the below example we are trying to fetch data from cell A4 or from some cell whose value will be changing based on some criteria. So we put that criterion in cell C3 and take this in by INDIRECT function as highlighted in red color. The benefit of using this is that we don’t need to adjust or reference every time.IT will be constant.
Create a Reference to a Different Sheet:
One more example of using reference but to a different sheet.
Refer to a Named Range:
In this example, we used NAME RANGE to refer and sum it to get value. Here we named cell B2 to E2 as Excel. And in the formula, we used INDIRECT to capture this in an array. Please note if we use directly Excel INDIRECT then we need to use a double quote otherwise we will follow the example below.
I am providing a link to make more clarity on this.
You can read about further excel INDIRECT formula from the below article.