We have all heard of arrays. Depending on the dimensionality, they are divided into one-dimensional arrays and two-dimensional arrays. Depending on the data type, they can also be divided into numeric arrays, logical arrays, and so on. The concatenation and operation of different arrays is an important part of excel formulas. Many people are still confused about the arithmetic operations of arrays. This article will introduce the basic operations between arrays, such as addition and multiplication.

Direct operations are performed on arrays by using operators instead of using functions. Since the components of arrays include numeric, textual, logical, and error values, arrays inherit the arithmetic properties of all types of data (except error values). Numeric and logical arrays can perform regular arithmetic operations such as addition, subtraction, multiplication, and division, while text arrays can perform concatenation operations.

Direct Operation Between Arrays and Values

Users can perform an arithmetic operation between an array and a number. The operation returns an array in the same dimension. This array contains the same number of elements. For example, if an M*1 array and a number are added, the new array is also an M*1 array.

See the example below. For vertical array, elements are separated by semicolon, and for horizontal array, elements are separated by comma.

Array: {1;2;3}

Value: 4

Operation: {1;2;3}+4

Result: {5;6;7}

The gif shows the operation between a vertical array {1;2;3} and a number {4}. The new array is also a vertical array contains three numbers {5;6;7}.

In some versions of excel, the area where the new array is to be saved should be selected in advance, then enter the operation in the first cell, and after entering the operation, press Shift+Control+Enter to return to the new array. However, in some versions, such as office 365, the user can enter the operation directly in E2 and then press Enter and the new array will be saved correctly in E2: E4.

The formula operation process is shown in the figure:

Direct Operations Between Two One-dimensional Arrays with Same Direction

For one-dimensional array, based on the directions we can divide array into one-dimensional vertical array and one-dimensional horizontal array.

When two one-dimensional arrays are operated directly, the elements in the same position will be operated correspondingly, and a new array is generated after the operation, which has the same direction and the same number of elements as the original array.

See the example below.

Array1: {1;2;3}

Array2: {4;5;6}

Operation: {1;2;3}+{4;5;6}

Result: {5;6;9}

The formula operation process is shown in the figure:

Here we should note that two one-dimensional arrays should have the same number of elements, otherwise some of the returned values will have an error #N/A.

See the example below.

Array1: {1;2;3}

Array2: {4;5;6;7}

Operation: {1;2;3}+{4;5;6;7}

Result: {5;6;9;#N/A}

Exceeded returns error #N/A. In this example, there is no value in array 1 corresponding to the fourth number {7} in array 2.

In addition to basic addition, multiplication, etc., arrays can be combined into a new array, an application that is widely used in everyday work when searching for something based on multiple conditions. The following example will show you the application in working life.

Example: Query the effort based on two conditions milestone and project.

In F3 enter the formula :

=INDEX(C2:C13,MATCH(F1&F2,A2:A13&B2:B13,0)).

In this example, the two conditions are “Milestone: 3” and “Project: Project B”, these two conditions are saved in columns A2:A13 and B2:B13. We use “&” to concentrate the two texts into one text and the two arrays into one new array. Then the formula uses the MATCH function to search for the location of the set “F1&F2” in the new array “A2:A13&B2:B13”, and the INDEX function returns the effort corresponding to “F1&F2” in C2:C13.

The arrays are as follows:

=INDEX({1200;1000;1100;1500;1200;1300;1400;1300;1000;1100;1500;1400},MATCH("3Project B",{"1Project A";"1Project B";"1Project C";"2Project A";"2Project B";"2Project C";"3Project A";"3Project B";"3Project C";"4Project A";"4Project B";"4Project C"},0))

Operation Between Two One-dimensional Arrays with Different Directions

Array1 is a vertically oriented array, it has one column but M rows, so it is an M*1 array with elements listed in each of the M rows; Array2 is a horizontally oriented array, it has one row but N columns, so it is a 1*N array with elements listed in each of the N columns.

The vertical array M*1 and the horizontal array 1*N are operated directly as follows: each element of array 1 is operated separately from each element of array 2, returning a two-dimensional array of M*N.

See the example below.

Array1: {1,2,3}

Array2: {4;5;6}

Operation: {1,2,3}+{4;5;6}

Result: {5,6,7;6,7,8;7,8,9}

The formula operation process is shown in the figure:

Operation Between One-dimensional Array and Two-dimensional Array

If a one-dimensional array and a two-dimensional array have the same dimensions in the same direction, for example the two-dimensional array is M*N, the one dimensional array is M*1 or 1*N, then they can be operated directly as follows: elements inside each array are operated correspondingly to form a new two-dimensional array of M*N.

See the example below.

Array1: {1;2;3}

Array2: {1,4;2,5;3,6}

Operation: {1;2;3}*{1,4;2,5;3,6}

Result: {1,4;4,10;9,18}

The formula operation process is shown in the figure:

If the dimensions of the one-dimensional array and the two-dimensional array are different, the result will contain the error value #N/A.

Array1: {1;2;3}

Array2: {1,4;2,5;3,6;1,1}

Operation: {1;2;3}*{1,4;2,5;3,6;1,1}

Result: {1,4;4,10;9,18;#N/A,#N/A}

Operation Between Two-dimensional Arrays

Two two-dimensional arrays with the same dimension can be operated directly on two elements at the same position and return a new two-dimensional array with the same dimension. For example, operating two arrays M*N will form a new array M*N.

See the example below.

Array1: {1,4;2,5;3,6}

Array2: {1,4;2,5;3,6}

Operation: {1,4;2,5;3,6}*{1,4;2,5;3,6}

Result: {1,16;4,25;9,36}

The formula operation process is shown in the figure:

If the sizes of the two two-dimensional arrays involved in the operation do not match, the resulting array takes the largest column and row of the two arrays as the new array dimensions, but the part of the array that exceeds the smaller dimension generates the error value #N/A.