Returning Arrays

This page is not referring to functions that can be entered using (Ctrl + Shift + Enter) because all functions can be entered as Array Formulas.
This page refers to functions that have a built-in capability to return multiple values.

link - support.office.com/en-gb/article/excel-functions-that-return-ranges-or-arrays-7d1970e2-cbaa-4279-b59c-b9dd3900fc69

Functions that always return multiple values

The following is a list of functions that always return an array.

FREQUENCY	The number of times a value occurs in a given array or cell range.
LINEST	The array of values for a straight line that best fits your data.

FREQUENCY The number of times a value occurs in a given array or cell range.
LINEST The array of values for a straight line that best fits your data.

Functions that can return multiple values

The following is a list of functions that can return an array, depending on the arguments.

CHOOSE	The value in a row (or column) based on an index number.
COLUMN	The column number of the cell reference.
FORECAST.LINEAR	The future value along a linear trend by using existing values.
GROWTH	The predicted exponential growth using existing data.
INDEX	The value from a cell range which is the intersection of a row AND a column.
LOGEST	The array of values for an exponential curve that best fits your data.
MINVERSE	The inverse matrix of an array.
MMULT	The matrix product of two arrays.
MUNIT	(2013) The unit matrix or the specified dimension.
OFFSET	The value in a cell which is an offset from another cell.
ROW	The row number of the cell reference.
SQRT	The positive square root of a number.
TRANSPOSE	The array with its orientation changed.
TREND	The y-values along a linear trend given a set of x-values.

CHOOSE The value in a row (or column) based on an index number.
COLUMN The column number of the cell reference.
FORECAST.LINEAR The future value along a linear trend by using existing values.
GROWTH The predicted exponential growth using existing data.
INDEX The value from a cell range which is the intersection of a row AND a column.
LOGEST The array of values for an exponential curve that best fits your data.
MINVERSE The inverse matrix of an array.
MMULT The matrix product of two arrays.
MUNIT (2013) The unit matrix or the specified dimension.
OFFSET The value in a cell which is an offset from another cell.
ROW The row number of the cell reference.
SQRT The positive square root of a number.
TRANSPOSE The array with its orientation changed.
TREND The y-values along a linear trend given a set of x-values.

Using Database Functions

If you are using array formulas to help filter out data by using conditions, then it is far more efficient to use the database functions instead.