ExcelFunctions.net
Excel Lookup and Reference Functions
Home »
Excel-Built-In-Functions »
Excel-Lookup-And-Reference-Functions
This page lists the current, built-in 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.
|
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
|
|
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
|
|
|
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
|
|
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 real-time data from a program that supports COM automation
|
|
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.
