Other Links
This is one of the 33 lessons from the
Tutorial on Excel
Lesson 3-13: Excel Lookup and Reference Functions and Formulas
There are 18 Excel functions in the text category. See them all with a brief description in appendix 18-G
Here are the functions that you will use more often with tips and examples.
| Functions | What it Does |
| INDEX | Uses an index to choose a value from a reference or array |
| MATCH | Looks up values in a reference or array |
| 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 |
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 Lesson 19A - Excel HLOOKUP Function
Excel Lesson 19B - Excel LOOKUP Function
Excel Lesson 19C - 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.
See an example with detailed explanations and instructions in the workbook "excel-example-dynamic-report.xls" (see the advertisement below)
This is one of the 33 lessons from the
Tutorial on Excel