returns “$A$1” as text. The INDIRECT function returns a valid reference from a text string. In the example shown, the ADDRESS function returns the value “$C$9” inside INDIRECT: The INDIRECT then this text into a normal reference and returns the value in cell C9, which is “Peach”. Note: INDIRECT is a volatile function and can cause performance problems in more complicated worksheets.
With INDEX
By feeding the INDEX function an array that begins at A1, and includes cells to reference, you can get the same result with a formula that may be easier to understand. For example, the formula below will return the same result as seen in the screenshot. The size of the array is arbitrary, but it must to start at A1 and include the data you wish to reference.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
