Lookup & Reference(Microsoft Excel Functions)

03/24/2024

Japanese version.

Functions related to cell address and value lookup.

Functions.

LOOKUP series functions. Find cells by value.

FunctionImportanceDescription
XLOOKUPHighGet data matching the search value.
VLOOKUPMiddleGet the value of a specific column in the row matching the search value.
HLOOKUPLowGet the value of a specific row of a column matching the search value.

Other Search Functions.

FunctionImportanceDescription
INDEXMiddleGet the values corresponding to the rows and columns of the specified range.
MATCHMiddleGet the relative position of the cell corresponding to the search value.
OFFSETMiddleGet the contents of a cell by specifying the distance from the reference cell, its height and width.
INDIRECTMiddleSpecify a cell address as a string and get the value of that cell.

Get information from a cell or cell range.

FunctionImportanceDescription
ROWHighGet the row position of the specified cell.
COLUMNHighGet the column position of the specified cell.
ROWSMiddleGet the number of rows in the specified cell range.
COLUMNSMiddleGet the number of columns in the specified cell range.
ADDRESSMiddleGet cell addresses by specifying row and column numbers
FORMULATEXTMiddleAcquire formulas in specified cells as strings.
AREASLowGet the number of regions within a specified cell range.

Manipulate cells and cell ranges

FunctionImportanceDescription
TRANSPOSEMiddleCreate a cell range with the rows and columns of the specified cell range swapped.
HYPERLINKMiddleSet hyperlinks to jump to Web pages or files on disk.

Spill functions

FunctionImportanceDescription
FILTERHighGet a list of data that matches the condition.
UNIQUEMiddleDelete duplicate data in a specified cell range.
SORTMiddleSort a range of cells by a specified column.
SORTBYMiddleSort a cell range by specifying multiple reference columns.

Array manipulation

FunctionImportanceDescription
DROPMiddleDelete a specified number of rows and columns from a specified cell range.
TAKEMiddleObtains a specified number of rows and columns from a specified cell range.
TOCOLLowArranges a specified range of cells in a single column.
TOROWLowSort a specified range of cells into a single column.
EXPANDMiddleExtended specified cell range.
CHOOSEROWSMiddleGet the row at the specified position from the specified cell range.
CHOOSECOLSMiddleGet the column at the specified position from the specified cell range.
WRAPROWSMiddleSort a range of cells in a single column or row by a specified number of rows.
WRAPCOLSMiddleSort a range of cells in a column or row by a specified number of columns.
VSTACKMiddleMerge multiple cell ranges in row direction.
HSTACKMiddleMerge multiple cell ranges in column direction.