This is an excerpt from Pierre Leclerc (www.excel-vba.com)

Excel functions and formulas

 

The 2 Most Important

Excel Functions AND
Formulas

 

SUMPRODUCT

AND

INDEX/MATCH

 

 

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

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

Functions What it Does
INDEX

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

MATCH

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
HLOOKUP

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
LOOKUP

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
VLOOKUP

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)

 

 

SUMPRODUCT  ¦   INDEX/MATCH  ¦   SUBTOTAL  ¦   ISERROR  ¦   HLOOKUP  ¦   LOOKUP  ¦   VLOOKUP
COUNTIF  ¦   SUMIF  ¦   IFERROR

 

left arrow Back home