Lookup and Reference Functions

ADDRESS

This function uses the row and column numbers to create a cell address in text.

AREAS

This function returns the number of areas in a reference.

CHOOSE

This function returns a value from a list of values.

CHOOSECOLS

This function returns the specified columns from an array.

CHOOSEROWS

This function returns the specified rows from an array.

COLUMN

This function returns the column number of a reference.

COLUMNS

This function returns the number of columns in an array.

DROP

This function excludes a specified number of rows or columns from the start or end of an array.

EXPAND

This function expands or pads an array to the specified row and column dimensions.

FILTER

This function filters a range or array.

FORMULATEXT

This function extracts data stored in a PivotTable.

GETPIVOTDATA

This function searches for a value in the top row and then returns a value in the same column from a specified row.

GROUPBY

This function creates a summary of your data using formula and supports grouping along row fields and aggregating the associated values.

HLOOKUP

This function creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet.

HSTACK

This function stack arrays horizontally.

HYPERLINK

This function creates a shortcut that opens documents stored on the Internet.

IMAGE

This function input an URL or a base64 string to show an image in cell.

INDEX

This function returns a value or the reference to a value from within an array or range.

INDIRECT

This function returns the reference specified by a text string. References are immediately evaluated to display their contents.

LOOKUP

This function searches for a value and returns a value from the same location in a second area.

MATCH

This function returns the relative position of a specified item in a range.

OFFSET

This function returns a reference to a range. The range is a specified number of rows and columns from a cell or range of cells. The function returns a single cell or a range of cells.

PIVOTBY

This function returns the group, aggregate, sort, and filter data based on the row and column fields that you specify.

ROW

This function returns the number of a row from a reference.

ROWS

This function returns the number of rows in an array.

SINGLE

This function returns a single value when given a value, range, or array.

SORT

This function sorts a range or array.

SORTBY

This function sorts a range or array based on the values in a corresponding range or array.

TAKE

This function returns a specified number of contiguous rows or columns from the start or end of an array.

TOCOL

This function returns the array in a single column.

TOROW

The function returns the array in a single row.

TRANSPOSE

This function returns a vertical range of cells as a horizontal range or a horizontal range of cells as a vertical range.

TRIMRANGE

This function excludes all empty rows and/or columns from the outer edges of a range or array.​​​​​​​​​​​​​​

UNIQUE

This function returns the unique values from a range or array.

VLOOKUP

This function searches for a value in the leftmost column and returns a value in the same row from a column you specify.

VSTACK

This function appends arrays vertically and in sequence to return a larger array.

WRAPCOLS

This function wraps the provided row or column of values by columns after a specified number of elements to form a new array.

WRAPROWS

This function wraps the provided row or column of values by rows after a specified number of elements to form a new array.

XLOOKUP

This function searches a range or an array for a match and returns the corresponding item from a second range or array. By default, an exact match is used.

XMATCH

This function returns the relative position of an item in an array. By default, an exact match is required.