Excel’s CHOOSE function with array

Image of software function sign fx
Create an array of values extracted from non-adjacent cells


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).
Video player icon

  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.