Lookup and Reference Functions help you to work with arrays of data. They include functions that return the location of a given address or value and functions to look up given values.

The Lookup and Reference functions have been grouped into categories to make it easy for you to find the function you need to perform your specific task. 

FunctionCategoryDescription
ADDRESSCell Range ReferenceReturns a reference as text to a single cell in a worksheet
AREASRow/Column InformationReturns the number of areas in a reference
CHOOSEData LookupChooses a value from a list of values
COLUMNRow/Column InformationReturns the column number of a reference
COLUMNSRow/Column InformationReturns the number of columns in a reference
FORMULATEXTOther Lookup/RefReturns the formula at the given reference as text (New in Excel 2013)
GETPIVOTDATAData LookupReturns data stored in a PivotTable report (New in Excel 2010)
HLOOKUPData LookupLooks in the top row of an array and returns the value of the indicated cell
HYPERLINKOther Lookup/RefCreates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet
INDEXCell Range ReferenceUses an index to choose a value from a reference or array
INDIRECTCell Range ReferenceReturns a reference indicated by a text value
LOOKUPData LookupLooks up values in a vector or array
MATCHData LookupLooks up values in a reference or array
OFFSETCell Range ReferenceReturns a reference offset from a given reference
ROWRow/Column InformationReturns the row number of a reference
ROWSRow/Column InformationReturns the number of rows in a reference
RTDOther Lookup/RefRetrieves real-time data from a program that supports COM automation
TRANSPOSEOther Lookup/RefReturns the transpose of an array
VLOOKUPData LookupLooks in the first column of an array and moves across the row to return the value of a cell