Using Index() as an array function

As promised, this post looks at using the Excel built in Index() function to operate on arrays; it also provides a VBA user defined function (UDF), VBIndex, which simplifies working with arrays within VBA, and also provides improved functionality when called from the spreadsheet.   A spreadsheet with full open-source code and the examples given below can be downloaded from:  VBIndex.

The Excel Index function returns the value of a single cell when provided with a row number and a column number.  If either the row or the column numbers are omitted then the function will return the full column or row respectively.  The screenshot below shows Index used to return the 4th row and the 3rd column of the sample data (see Useful Gayaan for more examples):

Using Index() to return an array

Using Index() to return an array

Note that the function must be entered as an array function to return all the data; see Using Array Formulas for details.

As well as a single value or omitted (or 0), the Index function will accept an array to define both the rows or columns to be returned.  The array may be a spreadsheet range (either a single row or column), or a list of values surrounded by {}.

Returning specified rows or columns with Index() and VBIndex()

Returning specified rows or columns with Index() and VBIndex()

The screenshot above shows the use of the UDF VBIndex() and the built-in Index() function. Note that when using the Index() function the list of rows must be entered as a column array (either a single column range, or values separated by a semi-colon), and the list of columns must be entered as a row array (either a single row range, or values separated by a comma). If this is not done correctly the returned array will not be the required values, as shown shaded in blue and pink.
To simplify the input, and also to simplify the use of the function from within VBA routines, I have written the UDF VBIndex, which will accept either a row or column array (or range) to specify both rows or columns. Examples are shown returning the 1st, 2nd, and 4th rows and columns with a variety of different forms of input.

This entry was posted in Arrays, Excel, UDFs, VBA and tagged , , , , , . Bookmark the permalink.

3 Responses to Using Index() as an array function

  1. wo3deameh says:

    Arrays are a great way to speed up Excel spreadsheets. Doing calculations in memory instead of individual cells is also more flexible. Don’t underestimate how powerful this can be. If you’ve never heard of APL, you might enjoy learning about what Kenneth Iverson invented. He thought Fortran was not expressive enough when it came to matrix calculations.
    If you are doing numerical calculations, gives better performance than Arrays are 1/3 the size of ranges and if you write C++ code they can give you a pointer to the array of doubles from Excel that can be passed to external libraries. Copying large arrays is a performance killer.


  2. Pingback: Daily Download 33: Miscellaneous | Newton Excel Bach, not (just) an Excel Blog

  3. Pingback: Slicing Arrays with VBA | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.