Excel Chapter 19 of 24: Excel Lookup and Reference Functions and Formulas

Excel offers you 18 functions in the logical category. Here are the ones (4) that you will use more often.

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
HLOOKUP	Looks in the top row of an array and returns the value of the indicated cell
LOOKUP	Looks up values in a vector or array
VLOOKUP	Looks in the first column of an array and moves across the row to return the value of a cell
INDIRECT	Returns a reference indicated by a text value
OFFSET	Returns a reference offset from a given reference

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" . You will also find applications in "excel-template-invoice.xls" 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". 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".

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"

Next Chapter: HLOOKUP Function in Excel

Excel Table of Contents
or use the site map link below

Discover Even More in 50 Excel spreadsheets

613-749-4695
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links

Excel Lookup and Reference Functions and Formulas