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.
The Excel INDEX function returns the value at a given location in a range or array. You can use INDEX to retrieve individual values, or entire rows and columns. The MATCH function is often used together with INDEX to provide row and column...