Lookup & Reference(Microsoft Excel Functions)

01/07/202310/21/2024

Japanese version.

Functions related to cell address and value lookup.

Functions.

LOOKUP series functions. Find cells by value.

Function	Importance	Description
XLOOKUP	High	Get data matching the search value.
VLOOKUP	Middle	Get the value of a specific column in the row matching the search value.
HLOOKUP	Low	Get the value of a specific row of a column matching the search value.

Other Search Functions.

Function	Importance	Description
INDEX	Middle	Get the values corresponding to the rows and columns of the specified range.
MATCH	Middle	Get the relative position of the cell corresponding to the search value.
OFFSET	Middle	Get the contents of a cell by specifying the distance from the reference cell, its height and width.
INDIRECT	Middle	Specify a cell address as a string and get the value of that cell.

Get information from a cell or cell range.

Function	Importance	Description
ROW	High	Get the row position of the specified cell.
COLUMN	High	Get the column position of the specified cell.
ROWS	Middle	Get the number of rows in the specified cell range.
COLUMNS	Middle	Get the number of columns in the specified cell range.
ADDRESS	Middle	Get cell addresses by specifying row and column numbers
FORMULATEXT	Middle	Acquire formulas in specified cells as strings.
AREAS	Low	Get the number of regions within a specified cell range.

Manipulate cells and cell ranges

Function	Importance	Description
TRANSPOSE	Middle	Create a cell range with the rows and columns of the specified cell range swapped.
HYPERLINK	Middle	Set hyperlinks to jump to Web pages or files on disk.

Spill functions

Function	Importance	Description
FILTER	High	Get a list of data that matches the condition.
UNIQUE	Middle	Delete duplicate data in a specified cell range.
SORT	Middle	Sort a range of cells by a specified column.
SORTBY	Middle	Sort a cell range by specifying multiple reference columns.
GROUPBY	High	Summary table by grouping by one axis.
PIVOTBY	High	Summary table by grouping by two axes.

Array manipulation

Function	Importance	Description
DROP	Middle	Delete a specified number of rows and columns from a specified cell range.
TAKE	Middle	Obtains a specified number of rows and columns from a specified cell range.
TOCOL	Low	Arranges a specified range of cells in a single column.
TOROW	Low	Sort a specified range of cells into a single column.
EXPAND	Middle	Extended specified cell range.
CHOOSEROWS	Middle	Get the row at the specified position from the specified cell range.
CHOOSECOLS	Middle	Get the column at the specified position from the specified cell range.
WRAPROWS	Middle	Sort a range of cells in a single column or row by a specified number of rows.
WRAPCOLS	Middle	Sort a range of cells in a column or row by a specified number of columns.
VSTACK	Middle	Merge multiple cell ranges in row direction.
HSTACK	Middle	Merge multiple cell ranges in column direction.