Array/Array Formula/Table Array

Arrays, Array Formulas, Table Arrays and how are they used in Excel

Excel Array Formulas
Excel Array Formulas. © Ted French

Array Overview

An array is range or group of related data values. In spreadsheet programs such as Excel and Google Spreadsheets the values in an array are normally stored in adjacent cells.

Uses for Arrays

Arrays can be used in both formulas (array formulas) and as arguments for functions such as the array forms of the LOOKUP and INDEX functions.

Types of Arrays

There are two types of array in Excel:

  • One - dimensional array
    • - also known as a vector or vector array - the data is located:
      • in single column (one-dimensional vertical array) or
      • in a single row (one-dimensional horizontal array);
  • Two - dimensional array - also known as a matrix
    • the data is located in multiple columns or rows .
  • Table Array - A  table array is one of the arguments used in Excel's lookup functions, such as VLOOKUP and HLOOKUP.

    The LOOKUP functions search the table array to find specific information.

    • For VLOOKUP (vertical lookup), the table_array must contain at least two columns of data.
    • For HLOOKUP (horizontal lookup), the table_array must contain at least two rows of data.

Array Formula Overview

An array formula is a formula that carries out calculations - such as addition, or multiplication - on the values in one or more arrays rather than a single data value.

Array formulas:

Array Formulas and Excel Functions

Many of  Excel's built-in functions - such as SUM, AVERAGE, or COUNT - can also be used in an array formula.

There are also a few functions - such as the TRANSPOSE function - that must always be entered as an array in order for it to work properly.

The usefulness of many functions such as INDEX and MATCH or MAX and IF can be extended by using them together in an array formula.

CSE Formulas

In Excel, array formulas are surrounded by curly braces "{ } ".

These braces cannot just be typed in, but must be added to a formula by pressing the Ctrl, Shift, and Enter keys after typing the formula into a cell or cells.

For this reason an array formula is sometimes referred to as a CSE formula in Excel.

An exception to this rule is when curly braces are used to enter an array as an argument for a function that normally contains just a single value or cell reference.

For example, in the tutorial below that uses VLOOKUP and the CHOOSE function to  create a left lookup formula, an array is created for the CHOOSE function's Index_num argument by typing the braces around the entered array.

Steps to Creating an Array Formula

  1. Enter the formula;
  2. Hold down the Ctrl and Shift keys on the keyboard;
  3. Press and release the Enter key to create the array formula;
  4. Release the Ctrl and Shift keys.

If done correctly, the formula will be surrounded by curly braces and each cell holding the formula will contain a different result.

Editing an Array Formula

 Any time an array formula is edited the curly braces disappear from around the array formula.

To get them back, the array formula must be entered by pressing the Ctrl, Shift, and Enter keys again just as when the array formula was first created.

Types of Array Formulas

There are two types of array formulas:

  • Multi cell array formula - the same formula is located in more than one cell in a worksheet;
  • single cell array formula one formula that carries out multiple calculations in a single worksheet cell.

Multi Cell Array Formulas

Like their name suggests, these array formulas are located in multiple worksheet cells and they also return an array as an answer.

In other words, the same formula is located in two or more cells and returns different answers in each cell.

How it doe this is that each copy or instance of the array formula performs the same calculation in each cell it is located in, but each instance of the formula uses different data in its calculations and, therefore, each instance produces different results.

An example of a multiple cell array formula would be:

{=A1:A2 * B1:B2}

Single Cell Array Formulas

This second type of array formulas use a function - such as SUM, AVERAGE, or COUNT, to combine the output of a multi-cell array formula into single value in a single cell.

An example of a single cell array formula would be:

{=SUM (A1:A2 * B1:B2)}