Displaying arrays

If the result of a formula is an array, you only see the top left entry in the array displayed on the worksheet.

Displaying the whole array

If you want to expand an array over a block of cells you can use the SET_VALUE function to see the array on the worksheet in full:

  1. Select a cell.
  2. Enter a formula using the SET_VALUE function, for example:
    SET_VALUE(E1:F5, A1)
    This writes the contents of an array held in cell A1 into cells E1 to F5
  3. Click the Tick button or press Return. The array named in the second parameter will be written into the range of cells defined in the first parameter.

Accessing a sub-array or a single array component

You can use the INDEX function to extract and display a section of an array or a single component from it.

For example:

INDEX(A1, 2, 3)

will return the component of the array held in cell A1 in the second column and third row of the array.

INDEX(A1, 1, 2, 2, 3)

will return a sub-array of two columns and three rows starting with the first column and second row.

If you extract a sub-array, you would still need to use SET_VALUE to display it, for example:

SET_VALUE(C3:D5, INDEX(A1, 1, 2, 2, 3))