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 - https://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.

FREQUENCYThe number of times a value occurs in a given array or cell range.
LINESTThe 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.

CHOOSEThe value in a row (or column) based on an index number.
COLUMNThe column number of the cell reference.
FORECAST.LINEARThe future value along a linear trend by using existing values.
GROWTHThe predicted exponential growth using existing data.
INDEXThe value from a cell range which is the intersection of a row AND a column.
LOGESTThe array of values for an exponential curve that best fits your data.
MINVERSEThe inverse matrix of an array.
MMULTThe matrix product of two arrays.
MUNIT(2013) The unit matrix or the specified dimension.
OFFSETThe value in a cell which is an offset from another cell.
ROWThe row number of the cell reference.
SQRTThe positive square root of a number.
TRANSPOSEThe array with its orientation changed.
TRENDThe 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.


© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext