Lookup and reference functions (reference)

black laptop computer on brown wooden table beside books
  • Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Starter 2010. 

To find detailed information about lookup and reference functions (reference), select its name in the first column.

An example of lookup and reference functions (reference).
Excel Easy, 2020.

Note: Version markers indicate the version of Excel a function was introduced. These lookup and reference functions (reference) functions aren’t available in earlier versions. For example, a version marker of 2013 indicates that this function is available in Excel 2013 and all later versions.

FunctionDescription
ADDRESS functionReturns a reference as text to a single cell in a worksheet
AREAS functionReturns the number of areas in a reference
CHOOSE functionChooses a value from a list of values
COLUMN functionReturns the column number of a reference
COLUMNS functionReturns the number of columns in a reference
FILTER functionOffice 365 buttonFilters a range of data based on criteria you define
FORMULATEXT function
Excel 2013
Returns the formula at the given reference as text
GETPIVOTDATA function
Excel 2010
Returns data stored in a PivotTable report
HLOOKUP functionLooks in the top row of an array and returns the value of the indicated cell
HYPERLINK functionCreates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet
INDEX functionUses an index to choose a value from a reference or array
INDIRECT functionReturns a reference indicated by a text value
LOOKUP functionLooks up values in a vector or array
MATCH functionLooks up values in a reference or array
OFFSET functionReturns a reference offset from a given reference
ROW functionReturns the row number of a reference
ROWS functionReturns the number of rows in a reference
RTD functionRetrieves real-time data from a program that supports COM automation
SORT functionOffice 365 buttonSorts the contents of a range or array
SORTBY functionOffice 365 buttonSorts the contents of a range or array based on the values in a corresponding range or array
TRANSPOSE functionReturns the transpose of an array
UNIQUE functionOffice 365 button Returns a list of unique values in a list or range
VLOOKUP functionLooks in the first column of an array and moves across the row to return the value of a cell
XLOOKUP functionOffice 365 buttonSearches 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 functionOffice 365 buttonReturns the relative position of an item in an array or range of cells. 

Important: The calculated results of formulas and some Excel worksheet functions may vary slightly between a Windows PC using x86 or x86-64 architecture and a Windows RT PC using ARM architecture. Learn more about the differences.

See Also

Excel functions (by category)

Excel functions (alphabetical)

Leave a Reply

%d bloggers like this: