MS Excel Lookup and reference functions (reference)

MS Excel Lookup and reference functions (reference) Important: 

Try using the new XLOOKUP function, an improved version of VLOOKUP that works in any direction and returns exact matches by default, making it easier and more convenient to use than its predecessor. 

 Watch Videos

Function	Description
ADDRESS function	Returns a reference as text to a single cell in a worksheet
AREAS function	Returns the number of areas in a reference
CHOOSE function	Chooses a value from a list of values
COLUMN function	Returns the column number of a reference
COLUMNS function	Returns the number of columns in a reference
FILTER function	Filters a range of data based on criteria you define
FORMULATEXT function	Returns the formula at the given reference as text
GETPIVOTDATA function	Returns data stored in a PivotTable report
HLOOKUP function	Looks in the top row of an array and returns the value of the indicated cell
HYPERLINK function	Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet
INDEX function	Uses an index to choose a value from a reference or array
INDIRECT function	Returns a reference indicated by a text value
LOOKUP function	Looks up values in a vector or array
MATCH function	Looks up values in a reference or array
OFFSET function	Returns a reference offset from a given reference
ROW function	Returns the row number of a reference
ROWS function	Returns the number of rows in a reference
RTD function	Retrieves real-time data from a program that supports COM automation
SORT function	Sorts the contents of a range or array
SORTBY function	Sorts the contents of a range or array based on the values in a corresponding range or array
TRANSPOSE function	Returns the transpose of an array
UNIQUE function	Returns a list of unique values in a list or range
VLOOKUP function	Looks in the first column of an array and moves across the row to return the value of a cell
XLOOKUP function	Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.
XMATCH function	Returns the relative position of an item in an array or range of cells.