PURPOSE

Create an array of values extracted from non-adjacent cells

SYNTAX

=CHOOSE({1,2,3},A1,A6,A8)

Syntax description

• The figures within the { } denote how many values the CHOOSE function needs to return.
• The list of cells (A1,A6,A8) gives the cell references to pull the values from.
• This is an array formula which requires Ctrl-Shift-Enter to run it.

METHOD — create an array of values using the CHOOSE function, and delete an array (see video below).

---

1. Highlight a range of cells equal in number to the number of values to be returned (3 in example above).
2. Enter the formula — as shown in the video you type in the first part of the formula, and then you can either type in the cell references, or cherry-pick the individual cells required, adding a comma in between.
3. With the cursor still in the formula bar, press Ctrl-Shift-Enter.
4. { } will appear around the formula and the values will load into the range of highlighted cells.
5. If insufficient cells have been highlighted to take all the values, re-select and extend the range of highlighted cells, and from within the formula bar, press Ctrl-Shift-Enter again.

Delete an array in Excel (also shown in video above)

To delete an array, highlight all the cells that contain the array, and then press delete (or right-click and select ‘Delete’ or ‘Clear contents’).

If you highlight just one cell from the array, and try to delete, an error pop-up states that it’s not possible to delete part of an array.

Related example

Detailed example: The function can be combined with the RIGHT function, and used to create an array of values to run the LINEST function.