The lookup and reference functions or array functions in Microsoft Excel (see Table 10-1) are the some of the best Excel has to offer. The reference functions VLOOKUP(), LOOKUP(), and HLOOKUP(), as well as INDEX() and MATCH(), provide an amazing array of tools for searching and finding information in a spreadsheet. In combination, they can be used to enhance the way you work.
Some functions return general information about cells (COLUMN(), ROW(), and ADDRESS()), and other functions return specific information (OFFSET(), COLUMNS(), and ROWS()). Initially you might be confused by all the names and possibilities—but practice makes perfect.
The Excel Function Wizard (in Microsoft Office Excel 2003 and earlier) provides the functions described in the following sections in the Lookup & Reference category. This category also includes the GETPIVOTDATA() function, which is explained in Chapter 13, and thus is not covered in this chapter.
Table 10-1. Overview of the Lookup and Reference Functions
Function | Description |
|---|---|
ADDRESS() | Converts a string into a cell reference |
AREAS() | Returns the number of contiguous ranges |
Returns the indexed value from a range | |
COLUMN() | Returns the column number of a reference |
COLUMNS() | Returns the number of columns in a range |
GETPIVOTDATA() | Calculates consolidated values from a Pivot range |
HLOOKUP() | Returns a value from an array in the specified row for the column that meets a set criteria in the first row |
HYPERLINK() | Creates a navigation link to files or web addresses |
INDEX() | Returns the value of a cell for a specified number of rows and columns from the upper-left corner of a range |
INDIRECT() | Returns the content of a cell specified by a text reference |
LOOKUP() | Looks up a value in a range and returns the corresponding value from a second range |
MATCH() | Returns the relative position of a value in an array that matches the criteria specified |
OFFSET() | Returns the reference of a cell offset by a certain number of rows and columns from a fixed reference |
ROW() | Returns the row number of a reference |
ROWS() | Returns the number of rows in a range |
RTD() | Queries data from a third-party application that supports COM automation |
TRANSPOSE() | Transposes the specified array |
VLOOKUP() | Returns a value from an array in the specified column for the row that meets a set criteria in the first column |