Unlike INDIRECT and OFFSET, INDEX is a non-volatile function. This means that INDEX will not recalculate whenever a change is made to a worksheet. This makes INDEX ideal for professional models and for worksheets that contain a large amount of data. However, INDEX is a complex function that takes time to understand. To keep things simple, we’ll build pieces of the final INDEX function step by step on the worksheet and then create a named range after we have the formula ready to go. Remember that we need to use absolute references since we’re creating a dynamic named range. First, let’s figure out the last row in this data. For that, we can use the COUNTA function with all of column A as the reference. The result is “11” since column A contains 11 non-empty cells. To calculate the last column, we can use COUNTA again; this time with all of row 1 as the reference. This returns “8”. Now let’s use INDEX to get a reference to the last cell in column A. To do this, we give INDEX all of column A as an array, and then we use the same COUNTA formula above to supply the row number. The result is the value in cell A11. Note that INDEX is actually returning a reference to cell A11, but the equal sign causes the reference to resolve to the cell value. To get the last cell in row 1, we give INDEX row 1 as the array; we leave “row” blank; and we use COUNTA with row 1 for the column number. The result is “Status” but, as before, INDEX is actually returning a reference to H1. Now let’s use INDEX to get a reference to the last cell in the data. For that, we need to give INDEX a range that’s bigger than our data will ever be. In this case, let’s use the first one-thousand rows of the worksheet. Next, we’ll use the COUNTA formulas we already figured out to supply both the column and row. The result is the string “Under contract.” But the key thing to remember, again, is that INDEX is actually returning a reference to cell H11. Finally, we’re ready to build the INDEX formula we’ll use to compute a dynamic range. This is the clever part. We know that the formula above is returning H11, the last cell in the range. So all we need to do is supply the first cell, A2, a colon, and then the formula above. The result is a formula that computes the full data range. If I use F9 to evaluate the index site of the range, we’ll see “Under contract,” but remember that this is actually a reference to cell H11; the last cell in the data range. We get a value error when I enter the formula because the result is a multi-cell array that Excel can’t display in a single cell. Now I’ll just copy this formula to the clipboard and use it as a reference to create a named range called “data.” When I click back out of the reference area, you can see that the correct range is highlighted on the worksheet. Finally, let’s make sure the range is dynamic. After I paste in some new data below, we can check the range again and see that it has indeed expanded to include the new property data.
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.
