Microsoft Office Tutorials and References

In Depth Information

**Understanding lookup and reference functions**

TABLE 14-3
LOOKUP function arguments

Argument

Description

The value, cell reference, or text (enclosed in quotation marks) you

want to find in a table or a range.

lookup_value

A cell range or name that defines the table in which to look.

table_array

The row or column number in the table from which to select the

result, counted relative to the table (not according to the actual row

and column numbers).

row_index_num

col_index_num

A logical value that determines whether the function matches

lookup_value
exactly or approximately. Type
FALSE
to match
lookup_

value
exactly. The default is TRUE, which finds the closest match.

range_lookup

A one-row or one-column range that contains numbers, text, or

logical values.

lookup_vector

A one-row or one-column range that must be the same size as

lookup_vector
.

result_vector

A range containing numbers, text, or logical values to compare with

lookup_value
.

array

The array form of LOOKUP determines whether to search horizontally or vertically based on

the shape of the table defined in the
array
argument. If the table has more columns than

rows, LOOKUP searches the first row for
lookup_value
; if the table has more rows than

columns, LOOKUP searches the first column for
lookup_value
. LOOKUP always returns the last

value in the row or column containing the
lookup_value
argument, or you can specify a row

or column number using VLOOKUP or HLOOKUP.

The VLOOKUP and HLOOKUP functions

For the VLOOKUP and HLOOKUP functions, whether Excel considers a lookup table to be

vertical or horizontal depends on where the comparison values (the first index) are located.

If the values are in the leftmost column of the table, the table is vertical; if they are in the

first row of the table, the table is horizontal. (In contrast, LOOKUP uses the shape of the

table to determine whether to use the first row or column as the comparison values.) The

comparison values can be numbers or text, but they
must
be sorted in ascending order. No

comparison value should be used more than once in a table.

The
index_num
argument (sometimes called the
offset
) provides the second index and tells

the lookup function which column or row of the table to look in for the function’s result.

The first column or row in the table has an index number of 1; therefore, the
index_num

argument must be greater than or equal to 1 and must never be greater than the number

of rows or columns in the table. For example, if a vertical table is three columns wide, the

index number can’t be greater than 3. If any value does not meet these rules, the function

returns an error value.