Excel 2000 in a Nutshell

Chapter 16. Lookup and Reference Functions

Excel offers a group of functions to locate or reference specific values within your workbook. For example, you may want to look up the sales totals for January or the number of books sold in Miami. This chapter provides a detailed description and examples for each of these functions.

The following functions are covered in detail in this chapter:

ADDRESS	HLOOKUP	OFFSET
AREAS	HYPERLINK	ROW
CHOOSE	INDEX	ROWS
COLUMN	INDIRECT	TRANSPOSE
COLUMNS	LOOKUP	VLOOKUP
GETPIVOTDATA	MATCH

Use these functions to do the following:

• Create a reference to a specific cell

• Select specific values

• Return data from a PivotTable report

• Add a hyperlink to your worksheet

• Create an indirect reference to a cell

• Transpose a range of cells

The Lookup and Reference Functions

Excel provides several different functions that are used to locate or reference values within a worksheet. These functions are covered in the remaining portion of this chapter in alphabetical order, but they can be categorized as follows:

Create references to cells:

ADDRESS (create a reference to a specific cell)

COLUMN (determine the column number)

HYPERLINK (create a shortcut to a document)

OFFSET (find specified number of columns and rows from base)

ROW (determine the row number)

TRANSPOSE (transpose vertical to horizontal range or vice versa

Count elements:

AREAS (determine the number of areas in a reference)

COLUMNS (determine number of columns in an array)

ROWS (determine the number of rows ...