Arrays (1)
1. An array
In an Excel worksheet, an array is a rectangular range of cells described by its dimension. Dimension is the number of rows (m), and number of columns (n). The number of rows is first, followed by the number of columns. For example, a 3 x 4 array (pronounced 3 by 4) has 3 rows and 4 columns. It is displayed as 3R x 4C in Excel.
Arrays are usually passed to, or returned by a formula or function.
- A column vector - is an array with one column and multiple rows. An m x 1 array
- A row vector - is an array with one row and multiple columns. A 1 x n array
- A multidimensional array - is an m x n array with multiple rows and multiple columns
Excel arrays are contained within a pair of curly braces. For example, {1,2,3,4} is a row vector with 4 elements (4 columns) ie. a 1 x 4 array. In applications such as Matlab, square brackets are used, [1,2,3,4].
2. Element-by-element operations
To perform element by element operations, each array must be of the same dimension, ie. the same number of rows and columns.
Example: A = {1,2,3,4}, and B = {5,6,7,8} are row vectors
Multiplication
A x B = {5,12,21,32}. This is equivalent to 1 x 5, 2 x 6, 3 x 7, 4 x 8
Division
A / B = {0.2,0.333333333,0.428571429,0.5}. This is equivalent to 1 / 5, 2 / 6, 3 / 7, 4 / 8
Example: C = {1;2;3;4}, D = {5;6;7;8}
Addition
C + D = {6,8,10,12}. This is equivalent to 1 + 5, 2 + 5, 3 + 7, 4 + 8
Subtraction
D - C = {6,8,10,12}. This is equivalent to 5 -1,
2.1 Functions that include array arguments
- FREQUENCY(data_array, bins_array)
- HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
- INDEX(array, row_num, [column_num])
- LOOKUP(lookup_value, array)
- MATCH(lookup_value, lookup_array, [match_type])
- MODE.MULT((number1,[number2],...) Note: Returns a vertical array
- MDETERM(array) Note: requires the CSE sequence
- MINVERSE(array) Note: requires the CSE sequence
- MMULT(array1, array2) Note: requires the CSE sequence
- SUMPRODUCT(array1, [array2], [array3], ...)
- SUMX2MY2(array_x, array_y)
- SUMX2PY2(array_x, array_y)
- SUMXMY2(array_x, array_y)
- TRANSPOSE(array) Note: requires the CSE sequence
- VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])



What is the dimension of the array?
By using the size of the selection. To determine the number of rows and columns:
- Use the address coordinates of the range.
- Navigating the selection
- left click and drag - dimension appears in the Name Box
- if the selection requires scrolling of the screen, the dimension appears near the fill handle, whilst the left button is depressed
- use a macro such as GetSelectDim
- Select the Current Region (Ctrl + A) then extend the selection one row (Shift + ) or one column (Shift + ), then revert back (Shift + in the case of a column). The Active Cell must the left column or top row.
- Published: 23 July 2015
- Revised: Saturday 25th of February 2023 - 10:13 AM, [Australian Eastern Standard Time (EST)]