Lookup & Reference Functions

ADDRESSThe cell reference given a row and column number.
AREASThe number of areas in a cell range or reference.
CHOOSEThe value in a row (or column) based on an index number.
CHOOSECOLS(2022) New. The array with just a specific number of columns.
CHOOSEROWS(2022) New. The array with just a specifiic number of rows.
COLUMNThe column number of a cell reference.
COLUMNSThe number of columns in a cell range or reference.
DROP(2022) New. The array without certain rows or columns.
EXPAND(2022) New. The array expanded or padded to specific dimensions.
FIELDVALUE(2020) New. The value in a particular field from a linked data type.
FILTER(2021) New. The array after filtering data that satisfies multiple conditions.
FORMULATEXTThe formula from a particular cell.
GETPIVOTDATAThe data obtained from a pivot table.
GROUPBY(2023) Preview. The grouping of your data along one axis and aggregates the associated values.
HLOOKUP(XLOOKUP) The value in the same column after finding a matching value in the first row.
HSTACK(2022) New. The array after putting multiple arrays next to each other.
HYPERLINKThe hyperlink linked to a cell, document or webpage.
INDEXThe value from a cell range which is the intersection of a row AND a column.
INDIRECTThe value of a given cell reference specified by a text string.
LET(2021) New. The result of a formula that can use variables.
LOOKUPThe value in a row (or column) that matches a value in a column (or row).
MATCH(XMATCH) The position of a value in a list, table or cell range.
OFFSETThe value in a cell which is an offset from another cell.
PIVOTBY(2023) Preview. The grouping of your data along two axis and aggregates the associated values.
ROWThe row number of a cell reference.
ROWSThe number of rows in a cell range or reference.
RTDThe real time data from a program that supports COM.
SINGLE(2021, Removed in 365) The value from a cell range which is the intersection of a row OR a column.
SORT(2021) New. The array of data that has been sorted by one column.
SORTBY(2021) New. The array of data that has been sorted based on multiple columns.
TAKE(2022) New. The intersection of specific rows and columns in an array.
TOCOL(2022) New. The array transformed into a single column.
TOROW(2022) New. The array transformed into a single row.
TRANSPOSEThe array with its orientation changed.
TRIMRANGE(2024) Preview. The array after excluding all empty rows and/or columns from the outer edges of a range.
UNIQUE(2021) New. The array of unique values in a list, table or cell range.
VLOOKUP(XLOOKUP) The value in the same row after finding a matching value in the first column.
VSTACK(2021) New. The array after putting multiple arrays on top of each other.
WRAPCOLS(2022) New. The array transformed into multiple columns.
WRAPROWS(2022) New. The array transformed into multiple rows.
XLOOKUP(2021) New. The value in the same row after finding a matching value in any column.
XMATCH(2021) New. The position of a value in a list, table or cell range.
ADDRESS
The cell reference given a row and column number.
AREAS
The number of areas in a cell range or reference.
CHOOSE
The value in a row (or column) based on an index number.
CHOOSECOLS
(2022) New. The array with just a specific number of columns.
CHOOSEROWS
(2022) New. The array with just a specifiic number of rows.
COLUMN
The column number of a cell reference.
COLUMNS
The number of columns in a cell range or reference.
DROP
(2022) New. The array without certain rows or columns.
EXPAND
(2022) New. The array expanded or padded to specific dimensions.
FIELDVALUE
(2020) New. The value in a particular field from a linked data type.
FILTER
(2021) New. The array after filtering data that satisfies multiple conditions.
FORMULATEXT
The formula from a particular cell.
GETPIVOTDATA
The data obtained from a pivot table.
GROUPBY
(2023) Preview. The grouping of your data along one axis and aggregates the associated values.
HLOOKUP
(XLOOKUP) The value in the same column after finding a matching value in the first row.
HSTACK
(2022) New. The array after putting multiple arrays next to each other.
HYPERLINK
The hyperlink linked to a cell, document or webpage.
INDEX
The value from a cell range which is the intersection of a row AND a column.
INDIRECT
The value of a given cell reference specified by a text string.
LET
(2021) New. The result of a formula that can use variables.
LOOKUP
The value in a row (or column) that matches a value in a column (or row).
MATCH
(XMATCH) The position of a value in a list, table or cell range.
OFFSET
The value in a cell which is an offset from another cell.
PIVOTBY
(2023) Preview. The grouping of your data along two axis and aggregates the associated values.
ROW
The row number of a cell reference.
ROWS
The number of rows in a cell range or reference.
RTD
The real time data from a program that supports COM.
SINGLE
(2021, Removed in 365) The value from a cell range which is the intersection of a row OR a column.
SORT
(2021) New. The array of data that has been sorted by one column.
SORTBY
(2021) New. The array of data that has been sorted based on multiple columns.
TAKE
(2022) New. The intersection of specific rows and columns in an array.
TOCOL
(2022) New. The array transformed into a single column.
TOROW
(2022) New. The array transformed into a single row.
TRANSPOSE
The array with its orientation changed.
TRIMRANGE
(2024) Preview. The array after excluding all empty rows and/or columns from the outer edges of a range.
UNIQUE
(2021) New. The array of unique values in a list, table or cell range.
VLOOKUP
(XLOOKUP) The value in the same row after finding a matching value in the first column.
VSTACK
(2021) New. The array after putting multiple arrays on top of each other.
WRAPCOLS
(2022) New. The array transformed into multiple columns.
WRAPROWS
(2022) New. The array transformed into multiple rows.
XLOOKUP
(2021) New. The value in the same row after finding a matching value in any column.
XMATCH
(2021) New. The position of a value in a list, table or cell range.

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top