Note: this formula is meant to define a named range that can be used in other formulas.
This page shows an example of a dynamic named range created with the INDEX function together with the COUNTA function. Dynamic named ranges automatically expand and contract when data is added or removed. They are an alternative to using an Excel Table, which also resizes as data is added or removed.
The INDEX function returns the value at a given position in a range or array. You can use INDEX to retrieve individual values or entire rows and columns in a range. What makes INDEX especially useful for dynamic named ranges is that it actually returns a reference. This means you can use INDEX to construct a mixed reference like $A$1:A100.
In the example shown, the named range "data" is defined by the following formula:
Here, we feed INDEX all of column A for the array, then use the COUNTA function to figure out the "last row" in the range. COUNTA works well here because there are 10 values in column A, including a header row. COUNTA therefore returns 10, which goes directly into INDEX as the row number. INDEX then returns a reference to $A$10, the last used row in the range:
This formula uses the OFFSET function to generate a range that expands and contracts by adjusting height and width based on a count of non-empty cells. The first argument in OFFSET represents the first cell in the data (the origin), which in this...
The Excel INDEX function returns the value at a given position in a range or array. You can use INDEX to retrieve individual values or entire rows and columns. INDEX is often used with the MATCH function, where MATCH locates and feeds a position to...
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.