Home »
ExcelBuiltInFunctions »
ExcelLookupAndReferenceFunctions
Excel Lookup and Reference Functions
This page lists the current, builtin Excel Lookup and Reference Functions. These functions are provided by Excel, to
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 functions listed below have been grouped into categories, to help you to find the function you need to
perform a specific task. If you select a function name, this will take you to a full description of the function, with
examples of use, tips & tricks, and common errors.
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 (ie. transforms a horizontal
range of cells into a vertical range and vice versa)

RTD

Retrieves realtime data from a program that supports COM automation

FORMULATEXT

Returns a formula as a string (New in Excel 2013)


See the Microsoft Office website
for an excerpt, taken from John Walkenbach's excellent book Excel 2007 Formulas, which gives several
examples of different uses of the Excel Lookup functions.