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.

CALCULATE

This function can be used to expand the group context when summarizing at the group level of a TableSheet.

CHOOSE

This function returns a value from a list of values.

COLUMN

This function returns the column number of a reference.

COLUMNS

This function returns the number of columns in an array.

FILTER

This function filters a range or array.

FORMULA_INDEX

This function returns a formula as a string.

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.

HLOOKUP

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

HYPERLINK

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.

REMOVEFILTERS

This function can be used to expand the group context when summarizing at the group level of a TableSheet.

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.

TRANSPOSE

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

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.

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.