Using INDIRECT to dynamically reference a cell in Excel
If you have ever wanted to use text to reference a cell then you will be
interested to know how the
INDIRECT function in Excel can help you out.
INDIRECT function accepts two parameters, but only one is required and
truthfully, you'll never use the second parameter. The parameter you will be
ref_text. It is the reference to a cell as a text string.
To give you an idea of how it works, take the following example spreadsheet:
=INDIRECT("B5") will reference the cell at B5.
Say you type 'B1' in cell A1 and you enter the number 10 in cell B1.
In cell C1 you enter the formula
=INDIRECT(A1), the result will be the
number 10. It works that way because
INDIRECT will take the text in cell A1
as the cell reference which in this case is 'B1' and the value in cell B1
The most useful application for this function is when you want to build a cell reference from text and/or numbers. For example, if you want to match values in one list to another and then return the value in the cell adjacent to the search list. Let's see how this would play out in Figure 1.
There is List 1 and List 2. The values in column A and B have been
typed in. In column D the values are typed. The formula used in cell E4
=INDIRECT("B" & (MATCH(D4,A$4:A$7,0)+3)).
The formula looks for a match of the letter c (cell D4) in the list in
column A and returns it's relative location if it finds a match (I added 3
because the list starts at row 4). So the match function will return the
relative location and we want to know the value adjacent to that cell which is
in column B. Using
INDIRECT we can construct the cell reference by adding
"B" and the result from the
MATCH function. It's that simple, just
use & to piece the parts together.