This is an excerpt from Pierre Leclerc (

Excel functions and formulas


The 2 Most Important

Excel Functions AND







Annex 7: Excel Spreadsheets Lookup and Reference Functions (18)

There are 18 functions in the "LookUp and Reference" category.

Functions What it Does

Uses an index to choose a value from a reference or array (Powerful in INDEX/MATCH Formulas)


Looks up values in a reference or array
(Powerful in INDEX/MATCH Formulas)

INDIRECT Returns a reference indicated by a text value
OFFSET  Returns a reference offset from a given reference
ADDRESS Returns a reference as text to a single cell in a worksheet
AREAS Returns the number of areas in a reference
COLUMN Returns the column number of a reference
COLUMNS Returns the number of columns in a reference
CHOOSE Chooses a value from a list of values
GETPIVOTDATA Returns data stored in a PivotTable

Looks in the top row of an array and returns the value of the indicated cell
(INDEX/MATCH Formula does better)

HYPERLINK Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet

Looks up values in a vector or array
(INDEX/MATCH Formula does better)

ROW Returns the row number of a reference
ROWS Returns the number of rows in a reference
RTD Retrieves real-time data from a program that supports COM automation  (Automation  : Façon de travailler avec les objets d'une application à partir d'une autre application ou d'un autre outil de développement. Autrefois appelée OLE Automation, Automation es
TRANSPOSE  Returns the transpose of an array

Looks in the first column of an array and moves across the row to return the value of a cell
(INDEX/MATCH Formula does better)





left arrow Back home