Add an index column to any dynamic array using a few of the latest Excel functions for easy lookups.
Contents:
How to Add an Index Column to a Dynamic or Spilled Array
Formula
This formula will add a numbered sequence, starting from 1, to the front of any array.
= HSTACK(SEQUENCE(ROWS(array)),array)Explanation
Let's say that we're working on a lookup table for a list of customers.
We're starting off with a dynamic array in cell C4# that is linked to the table "Customer_Table", and automatically updates as the customer table does.
It can be useful to add an index column (a numbered column that increases by 1 for each entry), but to do this automatically we need a few different functions. HSTACK, SEQUENCE, and ROWS.
= HSTACK(SEQUENCE(ROWS(array)),array)SEQUENCE Function
The first thing we need to understand is the sequence function. This function will create an incrementing list from any starting number. For example, the formula:
= SEQUENCE(5) 
Creates a dynamic array that begins with the number 1, and increases by 1 each row, spilling downward.
This creates the basis for our index column. The next thing we need to know is how many rows are in our original array.
Counting the Number of Rows in Array
To count the number of rows in our array, and also know the number that the sequence function should increase too, we can use the ROWS function.
The ROWS function counts how many rows currently exist in a dynamic or spilled array.
In this case, since our array is held in C4#, and is 6 rows long, the formula returns a 6.
This number is then fed into the SEQUENCE function, which creates a numbered list from 1 through 6.
Now we just need to combine our index column with the rest of the dynamic array.
Combining Two Dynamic Arrays
To do that we'll use the HSTACK function. With HSTACK we can horizontally combine two different arrays together.
In this case, the two arrays that we want to combine are the index column, using the formula:
= SEQUENCE(ROWS(C4#))As well as the rest of the array held in C4#.
Here are the two pieces we have to horizontally combine:
To combine them, we just need to drop both arrays into HSTACK together with our final formula:
= HSTACK(SEQUENCE(ROWS(C4#)),C4#)