Excel Lookup and Reference Functions

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

These functions help you to work with arrays of data, and are particularly useful when you need to cross reference between different data sets.

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

Excel Lookup and Reference Functions

Data Lookup Functions
HLOOKUP	Looks up a supplied value in the first row of a table, and returns the corresponding value from another row
VLOOKUP	Looks up a supplied value in the first column of a table, and returns the corresponding value from another column
LOOKUP	Searches for a specific value in one data vector, and returns a value from the corresponding position of a second data vector
GETPIVOTDATA	Extracts data stored in a Pivot Table
CHOOSE	Returns one of a list of values, depending on the value of a supplied index number
MATCH	Finds the relative position of a value in a supplied array

Row / Column / Area Information
ROW	Returns the row number of a supplied range, or of the current cell
COLUMN	Returns the column number of a supplied range, or of the current cell
ROWS	Returns the number of rows in a supplied range
COLUMNS	Returns the number of columns in a supplied range
AREAS	Returns the number of areas in a supplied range

Functions To Return References to Cell Ranges
ADDRESS	Returns a reference, in text format, for a supplied row and column number
INDEX	Returns a reference to a cell (or range of cells) for requested rows and columns within a supplied range
INDIRECT	Returns a cell or range reference that is represented by a supplied text string
OFFSET	Returns a reference to a range of cells that is a specified number of rows and columns from an initial supplied range

Other
HYPERLINK	Creates a hyperlink to a document in a supplied location.
TRANSPOSE	Performs a transpose transformation on a range of cells (i.e. transforms a horizontal range of cells into a vertical range and vice versa)
RTD	Retrieves real-time data from a program that supports COM automation
FORMULATEXT	Returns a formula as a string (New in Excel 2013)

Return to the List of All Built-In Excel Functions
Return to the ExcelFunctions.net Home Page