Excel Lookup and Reference Functions

This page lists the built-in Excel Lookup and Reference Functions.

These functions are provided by Excel, to help you to work with arrays of data, and are particularly useful when you need to cross reference between different data sets. They perform tasks such as providing information about a range, returning the location of a given address or value, or looking up specific values.

The functions have been grouped into categories, to help you to find the function you need. Each link takes you to a full description of the function, with examples of use and common errors.


Excel Lookup and Reference Functions

Data Lookup Functions
HLOOKUPLooks up a supplied value in the first row of a table, and returns the corresponding value from another row
VLOOKUPLooks up a supplied value in the first column of a table, and returns the corresponding value from another column
LOOKUPSearches for a specific value in one data vector, and returns a value from the corresponding position of a second data vector
GETPIVOTDATAExtracts data stored in a Pivot Table
CHOOSEReturns one of a list of values, depending on the value of a supplied index number
MATCHFinds the relative position of a value in a supplied array
Row / Column / Area Information
ROWReturns the row number of a supplied range, or of the current cell
COLUMNReturns the column number of a supplied range, or of the current cell
ROWSReturns the number of rows in a supplied range
COLUMNSReturns the number of columns in a supplied range
AREASReturns the number of areas in a supplied range
Functions To Return References to Cell Ranges
ADDRESSReturns a reference, in text format, for a supplied row and column number
INDEXReturns a reference to a cell (or range of cells) for requested rows and columns within a supplied range
INDIRECTReturns a cell or range reference that is represented by a supplied text string
OFFSETReturns a reference to a range of cells that is a specified number of rows and columns from an initial supplied range
Other
HYPERLINKCreates a hyperlink to a document in a supplied location.
TRANSPOSEPerforms a transpose transformation on a range of cells (ie. transforms a horizontal range of cells into a vertical range and vice versa)
RTDRetrieves real-time data from a program that supports COM automation
FORMULATEXTReturns a formula as a string (New in Excel 2013)