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

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()

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.