Lesson 7: Excel Spreadsheets Lookup and Reference Functions (18)
| 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)
|
The most important functions in this category
INDEX, MATCH
INDEX/MATCH formulas are so important that they are the subject of an entire workbook with step by step development instructions and examples. See "excel-tutorial-index-match.xls" (see the advertisement below). You will also find applications in "excel-template-invoice.xls" (see the advertisement below) where you enter the name of the client or select it in a drop down list and the address appears automatically in another cell, enter the product number and its description, unit and cost appear automatically. See also how to use INDEX/MATCH to develop scenarios and " what if" applications in business models "excel-example-scenario.xls" (see the advertisement below). You will also discover how to use INDEX/MATCH to develop cascading drop-down lists where you select a country in a first drop-down list and only cities from this country appear in the second drop-down list. See "excel-tutorial-ddlists.xls" (see the advertisement below).
INDEX/MATCH is also used to create relational databases in Excel. Suppose that the details about your clients are in one database and the sales are in another and you want to analyse sales by cities. Because the client's number is in both databases (sales and clients), you can analyze sales by city using an INDEX/MATCH formula to bring the cities into the sales database.
The LOOKUP Group
The functions in this group are widely known among advanced users. But once they discover the more powerful and less limited INDEX/MATCH they are kind of pushed aside. Click on the links below to access the pages of this website describing how they work and what is their limits.
Excel HLOOKUP Function
Excel LOOKUP Function
Excel VLOOKUP Function
Other Functions
When you start developing more complex business models or when you want to calculate and chart moving averages and moving " Year to Date" you will need the two following functions.
INDIRECT
If in cell A1 of Sheet1 you have this value (Sheet2!A1) and in cell A2 of Sheet1 you have the following formula:
=INDIRECT(A1) the result will be the value of cell A1 of Sheet2.
OFFSET
The most intellectually challenging function in Excel.
The general format of this function goes as follows:
=SUM(OFFSET(D1,1,1,3,3))
In plain English...sum the range of 3 rows by three columns that starts 1 row below and one column to the right of D1 (the anchor). So if you have 2 in all 9 cells E2 to G4 the result will be 18.
Lookup and Reference Functions in Excel
Links to the Top of the Page
Excel Functions and Formulas Sitemap
Downloadable Tutorials, Examples and Templates
|
