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 ... |
Get Excel 2000 in a Nutshell now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.