Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Convert text string to valid reference in Excel using Indirect function
  • How to use Excel VLOOKUP Function
  • MATCH function: Description, Usage, Syntax, Examples and Explanation
  • How to get address of first cell in range in Excel
  • How to use Excel MMULT Function

Data Analysis Examples

  • How to count table columns in Excel
  • How To Perform and Interpret Regression Analysis in Excel
  • Excel Frequency Function Example
  • Example of COUNTIFS with variable table column in Excel
  • Excel Bar Chart

Data Validation Examples

  • Excel Data validation unique values only
  • Excel Data validation date in specific year
  • Excel Data validation allow weekday only
  • Excel Data validation with conditional list
  • Prevent invalid data entering in specific cells

How to create dynamic named range with INDEX in Excel

by

This tutorials show examples one and two dynamic named ranges created.

The first is created with the INDEX function together with the COUNTA function. Dynamic named ranges automatically expand and contract when data is added or removed.

Formula

=$A$1:INDEX($A:$A,lastrow)

Explanation

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.

In the example shown, the named range “data” is defined by the following formula:

=$A$2:INDEX($A:$A,COUNTA($A:$A))

which resolves to the range $A$2:$A$10.

Worked Example:   Exact match lookup with SUMPRODUCT in Excel

How this formulas works

Note first that this formula is composed in two parts that sit either side of the range operator (:). On the left, we have the starting reference for the range, hard-coded as:

$A$2

On the right is the ending reference for the range, created with INDEX like this:

INDEX($A:$A,COUNTA($A:$A))

Here, we feed INDEX all of column A for the array, then use COUNTA 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:

INDEX($A:$A,10) // resolves to $A$10

So, the final result of the formula is this range:

$A$2:$A$10

A two dimensional range

The above example works for a one-dimensional range. To create a two-dimensional dynamic range where the number of columns is also dynamic, you can use the same approach, expanded like this:

=$A$2:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))

As before, COUNTA is used to figure out the “lastrow”, and we use COUNTA again to get the “lastcolumn”. These are supplied to index as row_num and column_num respectively.

Worked Example:   How to calculate next scheduled event in Excel

However, for the array, we supply the full worksheet, entered as all 1048576 rows, which allows INDEX to return a reference in a 2D space.

Worked Example:   How to get first column number in range in Excel

Note: Excel 2003 supports only 65535 rows.

Post navigation

Previous Post:

How to randomly assign people to groups in Excel

Next Post:

How to get address of first cell in range in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to strip numeric characters from cell in Excel
  • How to split text string at specific character in Excel
  • PROPER function: Description, Usage, Syntax, Examples and Explanation
  • Remove first character in a cell in Excel
  • How to abbreviate names or words in Excel
  • Convert text date dd/mm/yy to mm/dd/yy in Excel
  • Get fiscal year from date in Excel
  • Convert Excel time to Unix time in Excel
  • Get week number from date in Excel
  • Display Date is workday in Excel
  • Compound Annual Growth Rate CAGR formula examples in Excel
  • COUPDAYSNC function: Description, Usage, Syntax, Examples and Explanation
  • DOLLARDE function: Description, Usage, Syntax, Examples and Explanation
  • FV function: Description, Usage, Syntax, Examples and Explanation
  • COUPDAYBS function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles